On a scale of 1-10 of VBA knowledge I'm probably about .3. All the examples I've seen to add a new line of data to a table seem overly complicated but this was very simple and easy to learn. I created an actual form for my input so I had to make some minor changes but it works like a champ. Thank you for posting this.
💻Access 1,000+ Microsoft Excel video training tutorials covering Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams & MORE! JOIN TODAY TO ADVANCE YOUR EXCEL SKILLS 👉 www.myexcelonline.com/107-42.html
This is a great tutorial, thanks. But I keep getting an run time error 9. When I look at the vba, the set myRow is highlighted yellow and if I hover the mouse over it, it says myRow = nothing. I have tried it with different workbooks and I get the same error. I cant work out how to fix this.
Thanks for your reply. OK so I have downloaded the example one and I think I may have worked out why. After some playing around, I see that in the tutorial video, you write myrow. But when I look at the downloaded sample, the code shows myNewRow, not myRow. I have tried this on my workbook but still does not work. I deleted some of the records from the example on the defect data table and it gave me the same error that I was getting. When I cleared several of the last entries in the defect table and I added a new entry, it placed the new entry several lines below. @@MyExcelOnline
Thanks for the response. I have a few questions: 1. On the dim line when you first declare the video, what are you calling the row variable? You can use any name you would like as long as it remains consistent throughout the entire module. 2. If you use just the sample file, is that working for you?
I have managed to get it working by copying the sample file code, not the code on the video. The sample code has myNewRow, not myRow which I think was the problem. Thank you. It is working perfectly now. Very much appreciated 🙂@@MyExcelOnline
Oh, we are so glad you learned something! VBA can see intimidating at first, but it's really poweful! Hope you keep adding to your knowledge base in Excel.
How could I have the cell in the first column auto-increment up one number from the previous row (or up one from the highest number in the existing table), to basically create a primary key as the table expands?
Absolutely! You'd have to create an integer variable and then go to the end of the range of values and then add 1 to that value. Maybe something similar to this: dim rngNewCell as range set rngNewCell = Range("A1") or whatever you want your starting cell to be do until rngNewCell.value="" set rngNewCell=engNewCell.Offset(1,0) loop rngNewCell.value=rngNewCell.Offset(-1,0).value+1 This will work as long as the last cell has a number in it Hope that helps!
This is a great walkthrough that I've been looking for. When I try to follow your example, my data doesn't go to the bottom of the table. It is inserted in random places. Do you know what would cause this? (I'm just learning VBA)
Hi. So glad you want to learn VBA! Are you able to download our sample file from the link in the description and compare what you have written to what is in that file? I'd start there.
Thanks for the comment. Yes it is possible! We do have a consulting servie if you ever need further help. www.myexcelonline.com/microsoft-excel-consulting-services/
Hi. Thanks for the question. You can set your variable equal to the cell value using Range("A1").Value, for instance, if your value was in cell A1. Hope that helps.
Hi, what if if someone inserts a new row within the table defects wont this mess up the data output? How do I stop this from happening? Will I need to create individual data tables and call each one so the data never gets broken?
Hi, I need a way to Protect Worksheet with VBA coding allowing View, Copy, Amend Unlocked Cells. I saw that general Protect method can be eazilly removed. Many Thanks !!! 👍👍👍
This video was great and extremely helpful, but I was wondering if I could ask for a little more assistance? I need to add the data to a row but maintain the formula within specific cells. For example, when you use the formula "myRow.Range(1)=Range("B2")" this would take the information as it appears in the cell and enter it exactly, even if there was formula in the cell. Essentially it pastes values and not formulae. Is there a way to do this? Every other method I have looked at to do this deviates massively from what you have done to enter the row so getting them to match up is difficult, especially as my understanding of VBA code is extremely limited. Thanks :)
Are you asking to paste the formula instead of the value? If so, then try the formula method of the range like this: myRow.Range(1).Formula=Range("B2").Formula
What if our table already has values on a row that you want to input additional data? I have other codes on each row which makes the coding you provided believe those rows are active. So it goes all the way to the bottom of the table and adds a new row. how can I get around this?
No I figured it out. Your coding doesn't require me create a table with several rows already built. It just builds a new one and works much better than what I have. Thanks a lot!@@MyExcelOnline
Very helpful but when I try it my count of rows is 1 less than the number of rows in the table (e.g. count returns 9 when there are actually 10 rows, counting headers). Any ideas?
Hi Great video but I keep getting an error when I try and run it, the debugger highlights this line: (IntRows = ActiveWorkbook.Worksheets("Entry").ListObjects("DBT").ListRows.Count) as not being correct. Any insite?
Thanks for the question! VBA is picky. Were you able to download the template from the link in the video? That way you can compare the exact syntax with what you have. Sometimes I paste the lines one on top of the other in Notepad to make sure they are identical.
If I highlight the data in my table --> delete it --> resize table back to where it started --> run code again I am getting a: "Run-time error '9':" "Subscript out of range" I think it's because the cell it is trying to put the new record in is now out of the tables bounds after being resized.
Yes the code is picky. You could step through the sample file we have in the description. Or you can also contact our consulting services if you need further help at www.myexcelonline.com/microsoft-excel-consulting-services/#
@SalsaEdits were you able to figure out how to essentially reset "myRow" whenever you want to delete data and resize the table back to where it started?
hello. i have seen in this video at minute 6.54 that you have your table headers written right where excel columns headings are mentioned. like date instead of column heading A and time instead of column heading B. please explain how its done ?
Oh we did not change the column headers. It's just the table column header. If you go to Insert|Table you can turn your dataset into a table. Then the table can have its own header at the top. I am not sure what happened when we scrolled in the video, but as far as I know, there is no way to change the actual Excel column header from the standard A, B, C. Hope that helps.
This is a very usefull video. But I always get "Error 438: Object doesn't support this property or method. Can someone help? I copied my sheet and table name so they are identical.
So sorry to hear it's not working for you. Were you able to download the sample from the video description? VBA is very picky with its syntax. Sometimes I copy what I wrote in Notepad and then paste the line from a sample right under it to see if I am off. The line where it fails could be a good starting place. Compare what you have to the same line in the sample. And then work back to check the previous lines. Hope that helps! If not, we have consulting services that can help you with a specific file if you would like. Check out www.myexcelonline.com/microsoft-excel-consulting-services/#
Yes. You can write that in VBA. An easy way to do that would be to record a macro doing exactly that and see what code it creates and then add it to the existing macro.
Never mind. I notice at 6:41 when you show the bottom of your table that it contains a blank row. When I do this, the row count is still wrong but since the last row is blank, the data goes there.
I have another question. I followed your macro input and everything works up until I try to add a new entry or row to my table w/an Autofilter applied. Gives me Run-time error 1004. Is there a solve for that?
Thank you for your help! Just a question, is it possible to add the data to the top of the table instead of the bottom ? Because im trying to INDEX MATCH to this table however it retrieves the row of data from the top of the list. This is my code: Private Sub AddNPButton_Click() Dim npRow As ListRow Dim intRow As Integer
intRow = ActiveWorkbook.Worksheets("Database").ListObjects("MemberInfo").ListRows.Count Set npRow = ActiveWorkbook.Worksheets("Database").ListObjects("MemberInfo").ListRows.Add(intRow)
Thanks for the question! You could probably count the rows you would like to add, insert that number of rows at the top, and then do a copy/paste. Try recording a macro and see if you can modify it from there. If you would like further assistance, we have a consulting service that may be helpful for you. Check out www.myexcelonline.com/microsoft-excel-consulting-services/
Following this to the letter (or so I think) nothing happens when I use button, but no error triggers in code either. Moving data in Sheet2 to worksheet Data1, Table. Command Button is on Sheet2 Code as follows: Private Sub cmdadd_Click() Dim myRow As ListRow Dim intRows As Integer
intRows = ActiveWorkbook.Worksheets("Data1").ListObjects("Table1").ListRows.Count Set myRow = ActiveWorkbook.Worksheets("Data1").ListObjects("Table1").ListRows.Add(intRows)
Were you able to download the template in the description? That will allow you to compare the exact syntax. Sometimes it is helpful to see which line it is failing on and paste that into Notepad next to the line in the template. And then try working back from there. Hope that helps!
Hi. Thanks so much for your question! Can you please double check the syntax you have with this. Sometimes the spelling can be off. intNewRow = Worksheets("DefectData").ListObjects("tblDefects").ListRows.Count Set myNewRow = ActiveWorkbook.Worksheets("DefectData").ListObjects("tblDefects").ListRows.Add(intNewRow) Also, are you running the button from the Dashboard tab? You can download the practice workbook here: www.myexcelonline.com/blog/create-data-entry-form-excel/ Please let me know if that helps.
I have this same issue. The code works but I am trying to add multiple rows from a table at the same time. The code works if I rewrite the code again, but writing the same code 50 times is not efficient. I tried to do a loop but keep getting errors. Could you show the code to integrate the loop?
Sorry. That's frustrating! That usually means you may have moved into a cell that doesn't exist. You can set a break point and step through and use the immediate window to see why it is failing.
Saved my life. We have been working on project and also using Autofilter on a table . But data was being added below table not on a table. Now filter did not work on data below table. 🧑🦯
I was in tremendous trouble until this was found. This reduced time-wasting over data segregation. Simply
saved my day !!!!!
So glad you found us! Thanks for the comment.
Though almost after a year, this is very loud and clear, as any beginer could understand, no waste of time. Appreciate it.
Oh, so glad you liked it! Glad you learned something new.
On a scale of 1-10 of VBA knowledge I'm probably about .3. All the examples I've seen to add a new line of data to a table seem overly complicated but this was very simple and easy to learn. I created an actual form for my input so I had to make some minor changes but it works like a champ. Thank you for posting this.
Hi Tom. These are the comments we like the hear the most! So very glad you learned something new!. Thanks so much for watching.
looking for this step in my excel for the past few days ..finally found the perfect solution and it's working fine ....thanks a lot to u team
Glad it helped! Thanks for watching!
I was looking for this year's ago and now I'm looking for it again. Thank you. Definitely following this channel!
You're very welcome!
💻Access 1,000+ Microsoft Excel video training tutorials covering Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams & MORE! JOIN TODAY TO ADVANCE YOUR EXCEL SKILLS 👉 www.myexcelonline.com/107-42.html
THANK YOU!!! I’ve looking for this for months! 🙏
So glad you found it!!!
Thank you Thank you Thank you 🙏🙏🙏
You are so welcome! Thanks for watching!
So clearly explained and exaclty the simple solution I was looking for. Easy to execute and expand even though I'm a newbie. Thank you!
You are very welcome! Thank you for watching!
Very clearand defined guidance. Thank you.
Glad it was helpful! Thanks for taking the time to let us know!
Very clear. Thanks for helping by posting this video.
Glad it was helpful!
Thank you so much for this! I knew 0 about Excel in general and this video really helped me out with work!
That's great! So glad you watched it.
This is a great tutorial, thanks. But I keep getting an run time error 9. When I look at the vba, the set myRow is highlighted yellow and if I hover the mouse over it, it says myRow = nothing. I have tried it with different workbooks and I get the same error. I cant work out how to fix this.
Hi. So sorry to hear about your error. Are you able to download the sample in the link in the description to compare it to what you have?
Thanks for your reply. OK so I have downloaded the example one and I think I may have worked out why. After some playing around, I see that in the tutorial video, you write myrow. But when I look at the downloaded sample, the code shows myNewRow, not myRow. I have tried this on my workbook but still does not work. I deleted some of the records from the example on the defect data table and it gave me the same error that I was getting. When I cleared several of the last entries in the defect table and I added a new entry, it placed the new entry several lines below. @@MyExcelOnline
Thanks for the response. I have a few questions:
1. On the dim line when you first declare the video, what are you calling the row variable? You can use any name you would like as long as it remains consistent throughout the entire module.
2. If you use just the sample file, is that working for you?
I have managed to get it working by copying the sample file code, not the code on the video. The sample code has myNewRow, not myRow which I think was the problem. Thank you. It is working perfectly now. Very much appreciated 🙂@@MyExcelOnline
Oh ok, that's good for us to know. Thank you!
mind blowing ! , awesom lesson. thank you very much
My pleasure! So glad you liked it!
loved it. thank you very much!
So glad! Thanks for watching!
Thank you so much! What a great help this was.
You're very welcome! So glad it was helpful!
This is remarkable by all standards. Keep up
Thank you!
I don’t use VBA a lot. That being said, this tutorial is fantastic
Oh, we are so glad you learned something! VBA can see intimidating at first, but it's really poweful! Hope you keep adding to your knowledge base in Excel.
How could I have the cell in the first column auto-increment up one number from the previous row (or up one from the highest number in the existing table), to basically create a primary key as the table expands?
Absolutely! You'd have to create an integer variable and then go to the end of the range of values and then add 1 to that value. Maybe something similar to this:
dim rngNewCell as range
set rngNewCell = Range("A1") or whatever you want your starting cell to be
do until rngNewCell.value=""
set rngNewCell=engNewCell.Offset(1,0)
loop
rngNewCell.value=rngNewCell.Offset(-1,0).value+1 This will work as long as the last cell has a number in it
Hope that helps!
This is a great walkthrough that I've been looking for. When I try to follow your example, my data doesn't go to the bottom of the table. It is inserted in random places. Do you know what would cause this? (I'm just learning VBA)
Hi. So glad you want to learn VBA! Are you able to download our sample file from the link in the description and compare what you have written to what is in that file? I'd start there.
This is great. Is it possible to upgrade this to also remove data from table in the form of a drop down to select data already in the table?
Thanks for the comment. Yes it is possible! We do have a consulting servie if you ever need further help. www.myexcelonline.com/microsoft-excel-consulting-services/
Thanks!
What if you don't want to add a button and an input box, but want to input from another cell on the sheet?
Hi. Thanks for the question. You can set your variable equal to the cell value using Range("A1").Value, for instance, if your value was in cell A1. Hope that helps.
Hi, what if if someone inserts a new row within the table defects wont this mess up the data output? How do I stop this from happening? Will I need to create individual data tables and call each one so the data never gets broken?
Could you elaborate more? Do you want to stop the user from inputting into the table directly?
Hi, I need a way to Protect Worksheet with VBA coding allowing View, Copy, Amend Unlocked Cells. I saw that general Protect method can be eazilly removed. Many Thanks !!!
👍👍👍
Did you try ActiveSheet.Protect in your VBA code?
Thanx
No problem
This video was great and extremely helpful, but I was wondering if I could ask for a little more assistance? I need to add the data to a row but maintain the formula within specific cells. For example, when you use the formula "myRow.Range(1)=Range("B2")" this would take the information as it appears in the cell and enter it exactly, even if there was formula in the cell. Essentially it pastes values and not formulae. Is there a way to do this?
Every other method I have looked at to do this deviates massively from what you have done to enter the row so getting them to match up is difficult, especially as my understanding of VBA code is extremely limited. Thanks :)
Are you asking to paste the formula instead of the value? If so, then try the formula method of the range like this:
myRow.Range(1).Formula=Range("B2").Formula
Informative
Thanks! Glad you liked it!
What if our table already has values on a row that you want to input additional data? I have other codes on each row which makes the coding you provided believe those rows are active. So it goes all the way to the bottom of the table and adds a new row. how can I get around this?
Hi. Are you asking how to replace a specific row of data instead of adding a new one?
No I figured it out. Your coding doesn't require me create a table with several rows already built. It just builds a new one and works much better than what I have. Thanks a lot!@@MyExcelOnline
Oh, great! Glad you found a solution!
How do I see a portion of the table while entering data and how do I go to specific cell location to add particular data type?
Hi! You can use Range("the cell you want").select. So if you want to go to B2, you can put Range("B2").select.
Very helpful but when I try it my count of rows is 1 less than the number of rows in the table (e.g. count returns 9 when there are actually 10 rows, counting headers). Any ideas?
Yes, sometimes the numbering is off by 1 with a column header or a new record.
Hi Great video but I keep getting an error when I try and run it, the debugger highlights this line: (IntRows = ActiveWorkbook.Worksheets("Entry").ListObjects("DBT").ListRows.Count) as not being correct. Any insite?
Thanks for the question! VBA is picky. Were you able to download the template from the link in the video? That way you can compare the exact syntax with what you have. Sometimes I paste the lines one on top of the other in Notepad to make sure they are identical.
If I highlight the data in my table --> delete it --> resize table back to where it started --> run code again
I am getting a:
"Run-time error '9':"
"Subscript out of range"
I think it's because the cell it is trying to put the new record in is now out of the tables bounds after being resized.
Yes the code is picky. You could step through the sample file we have in the description. Or you can also contact our consulting services if you need further help at www.myexcelonline.com/microsoft-excel-consulting-services/#
@SalsaEdits were you able to figure out how to essentially reset "myRow" whenever you want to delete data and resize the table back to where it started?
@@user-uu6pb6yr7n No, I had to do some sort of workaround where it was like... Delete all contents except Row 1 of the table. Then resize the table
Hopefully that worked for you.
hello. i have seen in this video at minute 6.54 that you have your table headers written right where excel columns headings are mentioned. like date instead of column heading A and time instead of column heading B. please explain how its done ?
Oh we did not change the column headers. It's just the table column header. If you go to Insert|Table you can turn your dataset into a table. Then the table can have its own header at the top. I am not sure what happened when we scrolled in the video, but as far as I know, there is no way to change the actual Excel column header from the standard A, B, C. Hope that helps.
This is a very usefull video. But I always get "Error 438: Object doesn't support this property or method. Can someone help? I copied my sheet and table name so they are identical.
So sorry to hear it's not working for you. Were you able to download the sample from the video description? VBA is very picky with its syntax. Sometimes I copy what I wrote in Notepad and then paste the line from a sample right under it to see if I am off. The line where it fails could be a good starting place. Compare what you have to the same line in the sample. And then work back to check the previous lines. Hope that helps! If not, we have consulting services that can help you with a specific file if you would like. Check out www.myexcelonline.com/microsoft-excel-consulting-services/#
Is there a way to remove the previous entry or clear the cell for the next entry n the Dashboard after each time you click the "Add New" entry?
Yes. You can write that in VBA. An easy way to do that would be to record a macro doing exactly that and see what code it creates and then add it to the existing macro.
Instead of individual input, can you explain how to add data from a row on another table
I'll put your suggestion on our to do list. Thanks for the idea!
Never mind. I notice at 6:41 when you show the bottom of your table that it contains a blank row. When I do this, the row count is still wrong but since the last row is blank, the data goes there.
Oh, good. Glad you figured it out!
I have another question. I followed your macro input and everything works up until I try to add a new entry or row to my table w/an Autofilter applied. Gives me Run-time error 1004. Is there a solve for that?
Hi Sally! Thanks for watching! Are you able to take the filter off first? And then run the macro?
Nothing happened on my worksheet
Hi. Thanks for the question! Were you able to download the practice file and try that one?
Thank you for your help! Just a question, is it possible to add the data to the top of the table instead of the bottom ? Because im trying to INDEX MATCH to this table however it retrieves the row of data from the top of the list.
This is my code:
Private Sub AddNPButton_Click()
Dim npRow As ListRow
Dim intRow As Integer
intRow = ActiveWorkbook.Worksheets("Database").ListObjects("MemberInfo").ListRows.Count
Set npRow = ActiveWorkbook.Worksheets("Database").ListObjects("MemberInfo").ListRows.Add(intRow)
npRow.Range(1) = Range("B3")
npRow.Range(2) = Range("B5")
npRow.Range(3) = Range("B7")
npRow.Range(4) = Range("B9")
npRow.Range(5) = Range("B11")
End Sub
Thanks for the question! You could probably count the rows you would like to add, insert that number of rows at the top, and then do a copy/paste. Try recording a macro and see if you can modify it from there. If you would like further assistance, we have a consulting service that may be helpful for you. Check out www.myexcelonline.com/microsoft-excel-consulting-services/
Following this to the letter (or so I think) nothing happens when I use button, but no error triggers in code either. Moving data in Sheet2 to worksheet Data1, Table. Command Button is on Sheet2
Code as follows:
Private Sub cmdadd_Click()
Dim myRow As ListRow
Dim intRows As Integer
intRows = ActiveWorkbook.Worksheets("Data1").ListObjects("Table1").ListRows.Count
Set myRow = ActiveWorkbook.Worksheets("Data1").ListObjects("Table1").ListRows.Add(intRows)
myRow.Range(1) = Range("D3")
myRow.Range(2) = Range("D4")
myRow.Range(3) = Range("D5")
myRow.Range(4) = Range("D6")
myRow.Range(5) = Range("D7")
myRow.Range(6) = Range("D8")
myRow.Range(7) = Range("D9")
myRow.Range(8) = Range("D10")
myRow.Range(9) = Range("D11")
myRow.Range(10) = Range("D12")
myRow.Range(11) = Range("D13")
myRow.Range(12) = Range("D14")
myRow.Range(13) = Range("D15")
myRow.Range(14) = Range("D16")
myRow.Range(15) = Range("D17")
myRow.Range(16) = Range("D18")
myRow.Range(17) = Range("D19")
myRow.Range(18) = Range("D20")
myRow.Range(19) = Range("D21")
myRow.Range(20) = Range("D22")
myRow.Range(21) = Range("D23")
myRow.Range(22) = Range("D24")
myRow.Range(23) = Range("D25")
myRow.Range(24) = Range("D26")
myRow.Range(25) = Range("D27")
myRow.Range(26) = Range("D28")
myRow.Range(27) = Range("D29")
myRow.Range(28) = Range("D30")
myRow.Range(29) = Range("D31")
myRow.Range(30) = Range("D32")
myRow.Range(31) = Range("D33")
myRow.Range(32) = Range("D34")
myRow.Range(33) = Range("D35")
myRow.Range(34) = Range("D36")
myRow.Range(35) = Range("D37")
myRow.Range(36) = Range("D38")
myRow.Range(37) = Range("D39")
End Sub
Were you able to download the template in the description? That will allow you to compare the exact syntax. Sometimes it is helpful to see which line it is failing on and paste that into Notepad next to the line in the template. And then try working back from there. Hope that helps!
When I use macro I get RUNTIME ERROR 6
OVERFLOW
I tried to change the macro and add intRows = 10000000
But it did not work
Do you have a solution?
Hi. Thanks so much for your question!
Can you please double check the syntax you have with this. Sometimes the spelling can be off.
intNewRow = Worksheets("DefectData").ListObjects("tblDefects").ListRows.Count
Set myNewRow = ActiveWorkbook.Worksheets("DefectData").ListObjects("tblDefects").ListRows.Add(intNewRow)
Also, are you running the button from the Dashboard tab?
You can download the practice workbook here: www.myexcelonline.com/blog/create-data-entry-form-excel/
Please let me know if that helps.
THATS just for 4 rows to one line , if i have 16 cells shold added to 4 rows at one time
how do that?
You could use a do loop in the code with a counter to do it as many times as you would like!
I have this same issue. The code works but I am trying to add multiple rows from a table at the same time. The code works if I rewrite the code again, but writing the same code 50 times is not efficient. I tried to do a loop but keep getting errors. Could you show the code to integrate the loop?
@@rubenpulgar9810 just send me your code let me correct it for you
Run-time error '9' subscript out of range :))))))) this is why I don't bother with vba
Sorry. That's frustrating! That usually means you may have moved into a cell that doesn't exist. You can set a break point and step through and use the immediate window to see why it is failing.
Saved my life. We have been working on project and also using Autofilter on a table . But data was being added below table not on a table. Now filter did not work on data below table. 🧑🦯
That's great! Glad the video was helpful!