Macro to Create Table and Add to It

[johndoh453]

I was wondering if there was a way to create a table and populate it with certain data. Once the data has been populated, have the table be able to add rows if more data is added to the sheet.

I've added an example spreadsheet to the thread. The idea in the spreadsheet is that there are multiple "Data Sets." I've given them just some dummy headers and values. There are also different sets that will be read against the "Data Sets."

What I would like to see, if possible, is a macro that could populate the data in the way that it is set up now, and also be able to add rows if more "Data Sets" or different Sets are added to the sheet.

[MarvinP]

Hi johndoh452

Find the attached with the VBA I tink you want. Change the data in the top table and click the button to add it to the bottom of the table below.

[johndoh453]

Will this VBA work for only 2 sets of data? (cells D1:G6 and H1:K6) Or Can I continue to add data sets and have it work? Also is it the same for the sets in cells A1:B4? I might have set something up incorrectly, but it didn't seem to work when I added more data to the sheet. Thanks.

[MarvinP]

Hi johndoh453,

The bottom line is - you have to step through the VBA code and see what each step does. Then you need to modify it to meet your changing needs.

I was hoping you could use my code as an example to grow from.

My questions would be.
1. If you have more Data sets would the D1:G6 grow to the right?
2. If you have more Data Sets in A1:B4 would it grow down?
3. Would you need to insert blank rows if the above happens?

Read the code and step through it.

[johndoh453]

Hi johndoh453,

The bottom line is - you have to step through the VBA code and see what each step does. Then you need to modify it to meet your changing needs.

I was hoping you could use my code as an example to grow from.

My questions would be.
1. If you have more Data sets would the D1:G6 grow to the right?
2. If you have more Data Sets in A1:B4 would it grow down?
3. Would you need to insert blank rows if the above happens?

Read the code and step through it.

If I were to add more data sets, they would grow to the right, and also the sets would grow down if there were more to them. Looking at the code, I can see that it is set up for 2 data sets, I am just not fully sure on what needs to be edited in order to add more sets. If the size of the data set needs to increase, I understand where in the code the changes need to take place.

[MarvinP]

Hi Johndoh453,

This is always the problem with writing VBA for someone. I didn't know if you were going to fill in the D1:K6 table many times and add the data to the bottom of the table or extend the range down or to the right. I wrote two blocks of code with the second line of the first of:

Please Login or Register  to view this content.

and the second line of the second block with

Please Login or Register  to view this content.

If you exteded the Data to Data 3 and Data 4 to the right you would need to either add 2 of the same looking blocks of code or create a loop that went across the Data 1, Data 2 to the right in columns of 4 at a time.

[johndoh453]

Hi Johndoh453,

This is always the problem with writing VBA for someone. I didn't know if you were going to fill in the D1:K6 table many times and add the data to the bottom of the table or extend the range down or to the right. I wrote two blocks of code with the second line of the first of:

Please Login or Register  to view this content.

and the second line of the second block with

Please Login or Register  to view this content.

If you exteded the Data to Data 3 and Data 4 to the right you would need to either add 2 of the same looking blocks of code or create a loop that went across the Data 1, Data 2 to the right in columns of 4 at a time.

Thank you for the help. I've been able to have the code add more data to the table. However, it will not add which set it is (Set A, Set B, Set C). Where in the code is it listed to display this. It works fine for the first two sets of data, but if I tried to add anything to the right, it will only display the values, but not which set it is.

[MarvinP]

Hi John,
Find the attached with a few more loops but should do what you are trying to do.

[johndoh453]

Hi John,
Find the attached with a few more loops but should do what you are trying to do.

Thank you so much for your help.