copy corresponding sheet from three workbooks to one file

Hello,

I have the following workbooks and code. I need the loop to go through the three years of workbooks 2016 through 2018 and take the tab from each of the years files and save it down to one file per tab name so the ending file would have the three years from the one entity in one file and move to the next tabs etc. I have the code working to do two years but when I add a third year i get errors. The files are attached and the code is below. Can someone help please? The actual files have about 50 tabs in each.

Please Login or Register  to view this content.

Thanks. That did not work as designed. The way it works is I have the template and three files one for each year for a total of four files. I open the template macro and click run and it opens the three years files and copies each year from the three files for a specific tab number and drops it into one file and saves the file down with the tab name as the file name and then moves to the second tab and then the third and so on. It did work when I had just two years in the macro but as soon as I added the third year it is breaking down. I know it is probably a simple fix and I am not seeing it. It is where I added the 2018 workbook into the file where it already has a set variable so doesn't open the 2018 file to grab the tab I need. Thanks again for the help.

Your code is close to working. It is stacking all of the tabs for 2016 to one tab in the template and then 2017 to one tab and so on. I need it to grab Sheet A in the 2016 file, sheet A in the 2017 file and Sheet a in the 2018 file and paste them into the appropriate year tab and then save the file down with the tab name and then move to Tab B for 2016, 2017 and 2018 and paste each sheet into one file with the second tab name save it down and keep moving through until all unique tabs have files with a 2016, 2017 and 2018 tab in it.

Hi,

Thanks wb16 through wb17 and j were not defined. Once I defined them wb16 through wb17 as Workbook and j As long I end up getting subscript out of range on ThisWorkbook.Sheets(j).Copy Before:=ActiveWorkbook.Sheets(1)

I hope I explained myself appropriately.

An example is

Open 2016 file and grab tab 10003 and paste it to the 2016 tab of the template
Open 2017 file and grab tab 10003 and paste it to the 2017 tab of the template
Open 2018 file and grab tab 10003 and paste it to the 2018 tab of the template
Save as on the template naming the file 10003.
Then loop through the same steps for all of the tabs of the workbook so that I have one file for every company number showing their 3 years of financials.

thanks for the help. I really appreciate it. I started the file from another file I built out that did something similar but different with a summary and detail income statement. It has worked for probably the past 7 years. Now I have a new request for three year views. I altered the code from the other file I have and changed a few things on it. I tested it with two years and it works perfect. Problem is I have three years and I can't figure out what the issue is. I can post the two year version if that helps.

thanks. it created a file for every company that had its own tab however all of the files had company 00000 on each tab in every single file. The macro didn't loop to the next tab on the second file and then the third tab on the third file and so on. I will take a look at it. I appreciate the help.

wait let me check something. I had all the tabs with the data showing 00000. I will test it on the live data now.

yeah it is almost there but not working. Thanks anyway. it is throwing an error on the first file with the live data saying it can't do that to a merged cell and in that file the first two tabs have the right property tab in it but the third tab for 2018 has the property that is supposed to be in the next file and the workbooks have all of the same tab counts and names. I appreciate the help. It is just frustrating because I have it working for 2 years. the third messes me all up.

See if this is how you wanted.

Please Login or Register  to view this content.

that is exactly what i was looking for. Thank you! the code is much cleaner than the code that I had built in the original file I was using. Thanks for all the help and your time.