Multiple WBs to 1 Spreadsheet

[Bherner]

Hello and Good morning!

I am responsible for compiling a compilation of my company’s data from each source to a Master workbook, I need a macro that can be run from each divisions workbook (14) that will copy entire rows and columns from 1 sheet and stack onto a single sheet in my master workbook. The column number for each will be a constant number of 38. The row count will fluctuate.

Tab is Titled 'Inv_List'


I am sorry to trouble you all with this as I have trawled the forum and found some examples of similar actions but have not got the VBA knowledge to change them to what I need or to understand them properly enough to do it.

Thanks for your help in advance.

[Bherner]

Here is what I have so far, but I need only 1 sheet from each workbook for the new workbook, not everything.
Sub GetSheets()
Path = "PAth
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

[Decar]

Hi,

please use the code tags next time, makes it easier to read and is demanded by forum rules I think.

If you only have to copy the first sheet of each workbook then simply change the code to this

Please Login or Register  to view this content.

"Sheets(1)" could also be "Sheets("NameOfSheet"). The number describes the index number of the sheet and the second approach looks for the name of the sheet instead, but this would only make sense if each sheet you want to copy from the 4 workbooks has the same name.

[Bherner]

Thank you! I am not sure what the Code tags means but I apologize for breaking the rules.

I have entered the above, and once I hit run macro nothing is populating in the spreadsheet? Does the code need to be formatted a specific way for the macro to run?



thank you again!!!

[JOHN H. DAVIS]

Maybe:

With all 14 books open.

Please Login or Register  to view this content.

will copy each Sheet1 into Master

[Bherner]

I have made adjustments as listed above, opened all of the workbooks and entered the below macro code into the VBA application on a blank spreadsheet within the workbook I want to consolidate all of the data. I am basically trying to take the same table set from all 14 workbooks and stacking them on top of one another. Nothing is happening when I hit the run macro button. Please make adjustments to code as needed! Thank you, I am very lost!

Please Login or Register  to view this content.

[Decar]

Hi Bherner,

please create some example excel files, this will greatly reduce the solution finding process. Make like 3 example excel files where you want the data to be taken from, 1 compiler example file how it should look like when the macro finishes. E.g. 3 excel files called: ex1.xls, ex2.xls, ex3.xsl + compiler.xls and attach them here

Then this issue will be solved quicker and easier.

[Bherner]

Boys.xlsxDads.xlsxGirls.xlsxMoms.xlsxSummary.xlsx


I have attached 4 excel workbooks and a summary workbook. Each workbook has the 'Inv_List' tab that holds the data I need to pull into the summary.
As you can see in the summary workbook all of the data is copied and stacked on top of each other. That is how I currently am doing it but would love a macro to complete.

[Decar]

Hi,

I attached the finished file. The macro works only when you have a folder with the macrofile + the workbooks with the tab "Inv_list" included.

I dont think it is the most elegant way to do it but as long as you dont have to do it for 30 workbooks and 20000+ entries youshould not encounter any problems.

Please Login or Register  to view this content.

I hope this helped.

regards,
Decar

[Bherner]

Great!! So how do I use this now?

I saved the new macro file in the same folder location, opened it along with all of the other 4 files.

. I tried updating the highlighted in red part to the actual location but it kept saying 'Run-time error '1004' it could not locate the files ?

When I click debug the blue highlighted part populates.

Please Login or Register  to view this content.

[Bherner]

I am sure it is something very simple I am missing. If you could just plot out the steps and adjustments I need to make to the above code as well as the steps to actually running the macro, i.e. (opening all workbooks, updating the code in line ...)

thank you again Decar, you are incredible!!

[Decar]

Thank you :D

And I'm so sorry, I thought I tested it but it I forgot to set the Filename correctly. Actually I dont know why it does not work like this.

But just adjust the following and then it should be fine.

Please Login or Register  to view this content.

The steps to take are:
- put all the excel files with the tab "Inv_List" in one folder
- put the Summary file in this folder as
- run the code and it should combine all the values in your summary file

I am sorry for the late response but I guess the time difference does not help ^^.

let me know if it worked!

[Bherner]

Awesome!!! It is now running and getting much closer to being finished. I have altered the code and tried running in the final work book, I now receive the below error multiple times when running, I have tried clicking no all the way to the end because I simply want to stack the data. It will not finish and populate accurately on my actual file but works fine on the sample files.


"A formula or sheet you want to move or cope contains the name 'XYZ', which already exists on the destination worksheet. Do you want to use this version of the name?
-To use the name as defined in the desitination sheet, click yes.
-To rename the range referred to in the formula or worksheet,click no and enter a new name in the Name Conflict dialog box"

[Decar]

this happens when you copy a complete sheet that already has named ranges in it to another workbook that has the same named ranges. This would cause problems because now a named range called "ListA"for example would refer to sheet1 A:B and Sheet2 A:B which would conflict with each other.

I just tried to replicate the error but could not do it. I am not sure what is different with your code or how the other files are done. Maybe just delete the sensitive content and upload a version of the main workbook and one file which has to be copied so that I can try to figure out the problem.

[Bherner]

I opened a blank sheet to run the macro and stack the data, received the same error(s). However I just clicked through them all and the data did, for the first time, stack on the final page.

The first 16 rows of each file are really just an introductory and are filler. Is there an easy way to remove the first 16 rows?

[Decar]

good to hear that it works now.

to dismiss the first 16 rows just change the following part.

Please Login or Register  to view this content.

".cells(row, column)" is the syntax here. So now it will start not in the second row but in the 17th.



For the problem of clicking through all the workbooks when copying. Try to put the following in the beginning and end of your code. It should disable the warning, but make sure it activates in after again by adding the one with true at the end

This one directly after sub

Please Login or Register  to view this content.

This one directly above end sub

Please Login or Register  to view this content.

[Decar]

double post for some reason