Using VBA to Enter Data into an Excel Table

Рет қаралды 57,111

Күн бұрын

Watch this video to learn how to use VBA to Enter Data into an Excel Table using Microsoft Excel. In this VBA to Enter Data into an Excel Table tutorial, you are going to learn a few basic tips for entering data very quickly!
This is a great video for Beginner or Intermediate Excel users and it shows another way to do data entry to an Excel table with an introduction to writing VBA code.
This Using VBA to Enter Data into an Excel Table tutorial was recorded using Microsoft Office 365.
📥 Download this Using VBA to Enter Data into an Excel Table workbook so you can practice along with us: www.myexcelonline.com/blog/cr...
⌚ Time Stamps:
00:00 - Introduction
00:36 - Spreadsheet Description
01:01 - Saving an Excel File with VBA Code in it
01:35 - Data Validation
03:22 - Adding the Developer Ribbon
04:02 - Creating Active X Controls (button)
05:08- Writing Code
12:02 - Testing Code
12:53 - Preview of Next Lesson
13:32 - Review
------------
🔥 OUR BEST EXCEL RESOURCES 🔥
👨‍🏫 Get 30+ Excel & Office Courses & support for ONLY $1💵 (all levels covered)
Learn Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams, Project, PowerApps, PowerAutomate, Visio, Forms & MORE!
Join Now for ONLY $1 (LIMITED TIME OFFER) 👉 www.myexcelonline.com/107-186...
📚 Download Our Free 101 Excel Tips & Tricks E-Book:
Download for FREE 👉www.myexcelonline.com/101-exc...
💻 Access 1,000+ Free Excel & Office tutorials over at our award-winning blog:
Access for FREE 👉www.myexcelonline.com/107-3.html
📚Get All Our 101 Excel E-Book series on Formulas, Macros & Pivot Tables:
www.myexcelonline.com/107-59....
📚101 Most Popular Excel Formulas Paperback on Amazon:
www.amazon.com/dp/1700300911
📚101 Ready To Use Excel Macros Paperback on Amazon:
www.amazon.com/dp/1700729675
📚101 Best Excel Tips & Tricks Paperback on Amazon:
www.amazon.com/101-Best-Excel...
👷 Excel Consulting Services:
www.myexcelonline.com/microso...
💻 Looking for more Microsoft Excel & Office tutorials? Check out our playlist below:
www.youtube.com/@MyExcelOnlin...
------------
Feel free to comment and ask questions about this Microsoft Excel tutorial below and we will get back to you with the answer ASAP!
If you enjoyed the video, please give a thumbs up, comment, share.
Do not forget to SUBSCRIBE to this channel to get our new Microsoft Excel tutorials delivered straight to you each week! Thank You :)
Cheers,
JOHN MICHALOUDIS
Chief Inspirational Officer & Microsoft MVP
www.myexcelonline.com/
❤️ Let’s connect on social ❤️
LinkedIn: / johnmichaloudis
Instagram: / myexcelonline
Pinterest: / pins
Facebook: / myexcelonline
Twitter: / myexcelonline
#MyExcelOnline #MsExcel #VBA #Excel

Пікірлер: 103

@amilarambukwella510 Жыл бұрын

I was in tremendous trouble until this was found. This reduced time-wasting over data segregation. Simply saved my day !!!!!

@MyExcelOnline Жыл бұрын

So glad you found us! Thanks for the comment.

@FretMillen10 2 жыл бұрын

I was looking for this year's ago and now I'm looking for it again. Thank you. Definitely following this channel!

@MyExcelOnline 2 жыл бұрын

You're very welcome!

@azolotaiko 5 ай бұрын

THANK YOU!!! I’ve looking for this for months! 🙏

@MyExcelOnline 5 ай бұрын

So glad you found it!!!

@amilarambukwella510 Жыл бұрын

Though almost after a year, this is very loud and clear, as any beginer could understand, no waste of time. Appreciate it.

@MyExcelOnline Жыл бұрын

Oh, so glad you liked it! Glad you learned something new.

@ashwinsanjiv1996 Жыл бұрын

Very clear. Thanks for helping by posting this video.

@MyExcelOnline Жыл бұрын

Glad it was helpful!

@raymondpatterson6780 Жыл бұрын

Thank you so much! What a great help this was.

@MyExcelOnline Жыл бұрын

You're very welcome! So glad it was helpful!

@brunobb1 Жыл бұрын

Thank you so much for this! I knew 0 about Excel in general and this video really helped me out with work!

@MyExcelOnline Жыл бұрын

That's great! So glad you watched it.

@carabrookins5657 9 ай бұрын

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!

@MyExcelOnline 9 ай бұрын

You are very welcome! Thank you for watching!

@TradShowTom 2 жыл бұрын

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.

@MyExcelOnline 2 жыл бұрын

Hi Tom. These are the comments we like the hear the most! So very glad you learned something new!. Thanks so much for watching.

@assafadr 2 жыл бұрын

mind blowing ! , awesom lesson. thank you very much

@MyExcelOnline 2 жыл бұрын

My pleasure! So glad you liked it!

@GLOCK30S_45CAL Жыл бұрын

Very clearand defined guidance. Thank you.

@MyExcelOnline Жыл бұрын

Glad it was helpful! Thanks for taking the time to let us know!

@pickyiblinder9680 Жыл бұрын

loved it. thank you very much!

@MyExcelOnline Жыл бұрын

So glad! Thanks for watching!

@ponpandianm8576 2 жыл бұрын

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

@MyExcelOnline 2 жыл бұрын

Glad it helped! Thanks for watching!

@simpliside 2 жыл бұрын

This is remarkable by all standards. Keep up

@MyExcelOnline 2 жыл бұрын

Thank you!

@amilarambukwella510 Жыл бұрын

Thank you Thank you Thank you 🙏🙏🙏

@MyExcelOnline Жыл бұрын

You are so welcome! Thanks for watching!

@patrickschardt7724 2 жыл бұрын

I don’t use VBA a lot. That being said, this tutorial is fantastic

@MyExcelOnline 2 жыл бұрын

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.

@piytube1 Жыл бұрын

Informative

@MyExcelOnline Жыл бұрын

Thanks! Glad you liked it!

@alializadeh8195 Жыл бұрын

Thanx

@MyExcelOnline Жыл бұрын

No problem

@MyExcelOnline 2 жыл бұрын

💻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

@dmarks83 Жыл бұрын

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?

@MyExcelOnline Жыл бұрын

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/

@user-lf7zs9vr7z 9 ай бұрын

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)

@MyExcelOnline 9 ай бұрын

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.

@Krysda225 Жыл бұрын

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?

@MyExcelOnline Жыл бұрын

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.

@amilarambukwella510 Жыл бұрын

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 !!! 👍👍👍

@MyExcelOnline Жыл бұрын

Did you try ActiveSheet.Protect in your VBA code?

@francobertoli1989 4 ай бұрын

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.

@MyExcelOnline 4 ай бұрын

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?

@francobertoli1989 4 ай бұрын

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

@MyExcelOnline 4 ай бұрын

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?

@francobertoli1989 4 ай бұрын

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

@MyExcelOnline 4 ай бұрын

Oh ok, that's good for us to know. Thank you!

@zakirali1430 Жыл бұрын

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 ?

@MyExcelOnline Жыл бұрын

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.

@sallychan2344 2 жыл бұрын

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?

@MyExcelOnline 2 жыл бұрын

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.

@sallychan2344 2 жыл бұрын

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?

@MyExcelOnline 2 жыл бұрын

Hi Sally! Thanks for watching! Are you able to take the filter off first? And then run the macro?

@bertiebassett84 5 ай бұрын

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 :)

@MyExcelOnline 5 ай бұрын

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

@cuagainful Жыл бұрын

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?

@MyExcelOnline Жыл бұрын

Hi! You can use Range("the cell you want").select. So if you want to go to B2, you can put Range("B2").select.

@jamescarty96 3 ай бұрын

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?

@MyExcelOnline 3 ай бұрын

Could you elaborate more? Do you want to stop the user from inputting into the table directly?

@firemonkey7365 Жыл бұрын

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?

@MyExcelOnline Жыл бұрын

Yes, sometimes the numbering is off by 1 with a column header or a new record.

@rochesterworks9537 Жыл бұрын

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?

@MyExcelOnline Жыл бұрын

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.

@avmstephen9716 Жыл бұрын

Instead of individual input, can you explain how to add data from a row on another table

@MyExcelOnline Жыл бұрын

I'll put your suggestion on our to do list. Thanks for the idea!

@ericgutierrez7994 5 ай бұрын

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?

@MyExcelOnline 5 ай бұрын

Hi. Are you asking how to replace a specific row of data instead of adding a new one?

@ericgutierrez7994 5 ай бұрын

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

@MyExcelOnline 5 ай бұрын

Oh, great! Glad you found a solution!

@firemonkey7365 Жыл бұрын

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.

@MyExcelOnline Жыл бұрын

Oh, good. Glad you figured it out!

@robinnix3127 Жыл бұрын

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.

@MyExcelOnline Жыл бұрын

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/#

@tingchong1 Жыл бұрын

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

@MyExcelOnline Жыл бұрын

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/

@SalsaEdits Жыл бұрын

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.

@MyExcelOnline Жыл бұрын

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/#

@user-uu6pb6yr7n 8 ай бұрын

@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?

@SalsaEdits 8 ай бұрын

@@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

@MyExcelOnline 6 ай бұрын

Hopefully that worked for you.

@idanrefaeldayan1685 2 жыл бұрын

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?

@MyExcelOnline 2 жыл бұрын

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.

@ilyasstar9843 2 жыл бұрын

THATS just for 4 rows to one line , if i have 16 cells shold added to 4 rows at one time how do that?

@MyExcelOnline 2 жыл бұрын

You could use a do loop in the code with a counter to do it as many times as you would like!

@rubenpulgar9810 Жыл бұрын

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?

@ilyasstar9843 Жыл бұрын

@@rubenpulgar9810 just send me your code let me correct it for you

@MelissaDowden Жыл бұрын

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

@MyExcelOnline Жыл бұрын

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!

@b1dover Жыл бұрын

Run-time error '9' subscript out of range :))))))) this is why I don't bother with vba

@MyExcelOnline Жыл бұрын

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.

@BluFinanceTV Жыл бұрын

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. 🧑‍🦯

@MyExcelOnline Жыл бұрын

That's great! Glad the video was helpful!