Excel 2016 VBA to consolidate data from several workbooks into one book

[Kthom019]

Can someone help with a macro to consolidate data from several workbooks into one workbook. I recorded a macro by copying and pasting the data from their workbooks into the required worksheet in the new workbook. But my mind is saying that there is another way to do this; is there?

 

[My Aswer Is This]

Sure there is a better way then the recorder. But we would need a lot more specific details.
Like
1. All workbook names
2. Sheet names to copy.
3. Consolidate how?
4. Do you want all sheets in each workbook copied into one sheet on the Master workbook into just one sheet name "John"

Or what?

See we need all of these type details.

And all workbooks will have to be opened or the script will have to open the workbooks.
If that were the case we need the path to each workbook.

And you might want to show us the script the recorder wrote.

 

[Kthom019]

Okay, thanks for that info.

1. All workbook names
- JAS, BAS, TAS, BAR; those are the name of the workbooks.

2. Sheet names to copy.
- I am coping from sheet name "FCAST". Range D3 - O369

3. Consolidate how?
- the data will go into a workbook name "Forecast", into sheets named JAS, BAS, TAS, BAR

4. Do you want all sheets in each workbook copied into one sheet on the Master workbook into just one sheet name "John"
- after the all sheets are copied into the "Forecast" workbook, I have a sheet Named "TOTAL" which should consolidate all sheets JAS, BAS, TAS, BAR

All workbooks will be opened, no need for a VBA to open from source file.

I hope this information, can gain me some assistance; thanks in advance.

Sure there is a better way then the recorder. But we would need a lot more specific details.
Like
1. All workbook names
2. Sheet names to copy.
3. Consolidate how?
4. Do you want all sheets in each workbook copied into one sheet on the Master workbook into just one sheet name "John"

Or what?

See we need all of these type details.

And all workbooks will have to be opened or the script will have to open the workbooks.
If that were the case we need the path to each workbook.

And you might want to show us the script the recorder wrote.

 

[My Aswer Is This]

You said:

- the data will go into a workbook name "Forecast", into sheets named JAS, BAS, TAS, BAR

But where on these sheets will the data be entered?

And you said:

after the all sheets are copied into the "Forecast" workbook, I have a sheet Named "TOTAL" which should consolidate all sheets JAS, BAS, TAS, BAR

But did not say what is the name of this Workbook is and how do we consolidate this data?

Do we copy all the data from all these sheets into the One sheet.

​

_{^{<strike>
</strike>}}​

 

[My Aswer Is This]

Here is a example of how to do what you want.

I think it would be best to let you do this so you can learn.

Now you can see how to do this and just change the workbook names the sheet names and the ranges.

Sub Copy_Range()
'Modified  5/3/2019  11:14:06 PM  EDT
Workbooks("Userform 25.xlsm").Sheets("Gas").Range("A1:A30").Copy Workbooks("Copy Shapes.xlsm").Sheets("Harry").Range("G1")
End Sub

 

[Kthom019]

Okay.

You said:
- the data will go into a workbook name "Forecast", into sheets named JAS, BAS, TAS, BAR
- The data will be entered in range D3 - O369, for all sheets. I ensured the all sheets have the same headings and rows are alike.


And you said:

after the all sheets are copied into the "Forecast" workbook, I have a sheet Named "TOTAL" which should consolidate all sheets JAS, BAS, TAS, BAR. The data from the sheets should be copied not the sheets; sorry about that. Data from the workbooks JAS, BAS, TAS, BAR (the sheets in these workbooks have these same names) should be copied into the 'Forecast' workbook, (there are sheets in the 'Forecast' workbook bearing these same names.

But did not say what is the name of this Workbook is and how do we consolidate this data? The 'TOTAL' sheet is also in the 'Forecast' workbook. This sheet is giving a grand total of all rows and columns in sheets JAS, BAS, TAS, BAR. The 'Forecast' workbook has 5 sheets named: JAS, BAS, TAS, BAR & TOTAL. All sheets have the same number of rows and columns.

Do we copy all the data from all these sheets into the One sheet. The data from all 4 workbooks are being copied to the liked named sheets in the 'Forecast' workbook, thereafter, the 'TOTAL' sheet will be giving the grand total of all sheets, in the 'Forecast' workbook.

 

[Kthom019]

Yes, I want to learn

 

[Kthom019]

I received 'run-time error, Script out of range'.

Workbooks("Userform 25.xlsm").Sheets("Jas").Range("B4:Q6").Copy Workbooks("Copy Shapes.xlsm").Sheets("Forecast").Range("B4")

 

[Fluff]

Is this what you need?

Sub Kthom019()
   Dim Ary As Variant
   Dim i As Long
   Dim Wbk As Workbook
   
   Set Wbk = Workbooks("Forecast.xlsm")
   Ary = Array("JAS", "BAS", "TAS", "BAR")
   
   For i = 0 To UBound(Ary)
      Workbooks(Ary(i) & ".xlsm").Sheets("Fcast").Range("D3:O369").Copy Wbk.Sheets(Ary(i)).Range("D3")
   Next i
End Sub

Change file extension if needed.

 

[My Aswer Is This]

That is because I gave you a example. You need to change the Workbook names and sheet names to meet your needs.

See I'm trying to teach you how do this and not do it for you.
And you need to change the range to meet your needs.

I received 'run-time error, Script out of range'.

Workbooks("Userform 25.xlsm").Sheets("Jas").Range("B4:Q6").Copy Workbooks("Copy Shapes.xlsm").Sheets("Forecast").Range("B4")