Help with setting up a VBA

[Newbie73]

Hello, I was wondering if I could aumtomate the following in Excel, perhaps with the help of VBA (my first one with VBA)

Example VBA.xlsx

The example spreadsheet has 3 sheets. The raw data has the bulk of the data, here I would like for K2 to be copied down until the end of the table, It's a date so I need the same data to be copied down. Same with L2 (it usually just a word if it matters).

Then I would like everything (apart from the header, so row 1) to be copied to the sheet Data to the exact same columns order (which I named from 1 to 14, it's important that 2 goes to 2, 14 to 14 etc), the Data sheet has a formula on A2 through all the sheet and this is necessary but it's already there, no need to do anything.

I've done the Data wanted result sheet just to show how it's supposed to look. Despite some columns between 2 and 13 being blanked all needs to be copied over as sometimes this columns will have values

Is this doable and is VBA the way? This would repeat itself for many sheets, so all the copied information would be put right at the end of the previous information on the Data sheet (so that there's no blank rows)

Do let me know if I explained this correctly or if you need any more information or if this is even doable! Thank you

 

[Newbie73]

Thanks for your reply Alex, I've named R1 "Sheet Name" but where do I change it in the code? Or is it not necessary?

Also, the code at the moment is giving me an error "Invalid Next control variable reference", any ideas? Sorry about it

 

[Alex Blakenburg]

Sorry I made a last minute change. Change Next x to Next i
The heading won't affect the code.

 

[Newbie73]

Sorry I made a last minute change. Change Next x to Next i
The heading won't affect the code.

Thanks the code runs now! All works perfectly but if I run the macro again, it will still do the sheets that did previously

 

[Alex Blakenburg]

It shouldn't. It should append any new sheets you put on the right side of the Data Tab

To test:
Move 1 or more of the sheets to the left of the Data sheet.
Run the macro.
If you put a filter on column R or go to the bottom of R it should only have picked up the sheets to the right of the Data sheet.
Now move the sheets you moved to the left of the Data sheet to the right.
Run the macro again.
It should now have appended those sheets to the bottom of the Data sheet.

 

[Newbie73]

It shouldn't. It should append any new sheets you put on the right side of the Data Tab

To test:
Move 1 or more of the sheets to the left of the Data sheet.
Run the macro.
If you put a filter on column R or go to the bottom of R it should only have picked up the sheets to the right of the Data sheet.
Now move the sheets you moved to the left of the Data sheet to the right.
Run the macro again.
It should now have appended those sheets to the bottom of the Data sheet.

Yeah I just tried it again, a new sheet (just a copy of the other ones but different name. It will copy the two current sheets (their name appears in column R). Then I move a sheet to the right of Data, so between Data and the sheets that were already there and run the macro. The 3 sheets are then copy again to Data (instead of only the new one)

I can update the example sheet for you to have a look yourself if it's helpful

 

[Alex Blakenburg]

Are you saying that you end up with the original 2 sheets copied in twice ?
That seems really unlikely as long as you don't change the name of the sheets.
Do a record count of each of the sheets and total them up ?
Then run the macro with 2 sheets, then add the 3rd and run it again.
Does the record count in Data now = the total record count of the 3 sheets ?

 

[Newbie73]

Are you saying that you end up with the original 2 sheets copied in twice ?
That seems really unlikely as long as you don't change the name of the sheets.
Do a record count of each of the sheets and total them up ?
Then run the macro with 2 sheets, then add the 3rd and run it again.
Does the record count in Data now = the total record count of the 3 sheets ?

Sorry it took me a bit longer to reply, I had calculations off so I manually did them between every step but it still didn't work. At the end I would have the total of 5 sheets (original 2, then 3 more which is the previous two and the new one).

I will start updating the example sheet (and to see if it happens there or not)

 

[Newbie73]

Here it this:

Example VBA.xlsm

Sample example. The sheets are currently on the left side so that you can give it a try if you want (it's the ones named with Dates). I tried it there, moved two sheets and ran the macro all good. added the last sheet to the right as well and ran the macro again and it would add the new sheet first and then copy the previous ones again

Maybe I am doing something wrong? I copied your code and simply replaced the x with a i as you said

 

[Alex Blakenburg]

That's why a visual always helps and the key is to provide sample data that has the same characteristics as your real data.
Just add the line in Blue to your code and see if that fixes it.

    wsData.Columns("R").NumberFormat = "@"
    For i = wsData.Index + 1 To Sheets.Count

as long as you don't change the name of the sheets.

as long as you don't change the name of the sheets.

The sheet name wasn't changing but in column R it was being recognised as date and converted to a date.

 

[Newbie73]

That's why a visual always helps and the key is to provide sample data that has the same characteristics as your real data.
Just add the line in Blue to your code and see if that fixes it.

    wsData.Columns("R").NumberFormat = "@"
    For i = wsData.Index + 1 To Sheets.Count

as long as you don't change the name of the sheets.

The sheet name wasn't changing but in column R it was being recognised as date and converted to a date.

Perfect! It works now, thank you so much Alex for your help and time!