Hi - Id love some help with some code to reformat a workbook from the 'original format.xlsx' to the 'desired output.xlsx'. It involves taking each tab, converting it to columnar format, then combining all tabs into a consolidated columnar tab.
I can look at this for you.
You say you want to take the data from all tabs and combine the reformatted data into a combined data table.
Does that mean that you would run the macro just once at the en of the year to reformat & combine for the whole of that year?
Or do you want to run a macro at the end of each month and add that month's data to the "Combined" Table?
Or something else?
Id like to run it every week, so if the code could just combine all the tabs and all data every time I run it that would be a dream come true
If you run the code weekly then I do not understand why you want to go through every tab and reformat & combine all data.
For example if you run the code on Monday, November 26th, all data already in the combined table will be from the first week in Jan to the 2nd week in Nov. That data will not have changed (the only new data in the Original file will be data from Nov 19 onward.
That being the case, all the code needs to do is pick up the new data in Original, reformat it and add it to the existing data in the Combined Table.
I can do the code to redo the whole Combined Table every time the code is run, but to me that just seems to be a waste of code run time.
Let me know what you think.
lets do your suggestion - reformatting it and add it to the existing data. That would be awesome thank you!
Your file is ready. I will PM you with my PayPal details adattach the files here upon receipt of payment.
Payment received, many thanks.
Here are the 2 sample files
I had to modify your "original format" file because the Nov tab was showing Oct-18 in A1 in stead of Nov-18.
The "desired output" is now .xlsm as it contains the macro.
I did the code so that whenever the button is clicked the Combined Table will be updated to either the maximum date in the Data file or the date when the button is clicked.
Cell G1 shows the current maximum date for the Combined data, after clicking the button that will also update (in the case of this sample file the new maximum date should show as 11/18/2018).
Note that when you transfer the code to your actual file you will need to make a change in the code where indicated so that the code will use the correct data file (that file can be either open or closed).