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?

 

[Kthom019]

Message received: Run-time error 9, 'Subscript out of range'
I entered the below argument. I am not sure what is wrong. May you kind to help me understand the script and why the error, thanks.

Sub Consolidate_Files()
Dim Ary As Variant
Dim i As Long
Dim Wbk As Workbook

Set Wbk = Workbooks("Forecast test1.xlsm")
Ary = Array("Jas", "Bas", "Tas", "Bar")

For i = 0 To UBound(Ary)
Workbooks(Ary(i) & ".xlsm").Sheets("Jas").Range("D3:O369").Copy Wbk.Sheets(Ary(i)).Range("D3")
Next i
End Sub

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]

See my comments in post 10

 

[Fluff]

What is the name of the sheet that you are trying to copy from?

 

[Kthom019]

This was what I did.

'Workbooks("Userform 25.xlsm").Sheets("Jas").Range("A1:A30").Copy Workbooks("Copy Shapes.xlsm").Sheets("Jas-1").Range("D6")'

Remember, I am attempting to Copy data from workbook 'Jas' range D6:0360 into Workbook "Forecast - worksheet-'Jas-1', range D6:O360"

 

[Kthom019]

I am copying data from workbooks Jas, Bas, Tas, & Bar, worksheet name "FCAST" into workbook Forecast test1, with worksheets have the same names, Jas, Bas, Tas, & Bar.
example: data from the 'Jas' workbook sheet' FCAST', is being copied into workbook 'Forecast test1' worksheet 'JAS'
data from the 'Bas' workbook sheet' FCAST', is being copied into workbook 'Forecast test1' worksheet 'BAS'
data from the 'Tas' workbook sheet' FCAST', is being copied into workbook 'Forecast test1' worksheet 'TAS'
data from the 'Bar' workbook sheet' FCAST', is being copied into workbook 'Forecast test1' worksheet 'Bar'
After all sheets are copied over, I want the 'Total" sheet, which is also in workbook 'Forecast test1', to consolidate the records from all 4 files into one (summing all 4 sheets)
The ranges in all sheets are the same. D6:O360.

Sub Consolidate_Files()
Dim Ary As Variant
Dim i As Long
Dim Wbk As Workbook

Set Wbk = Workbooks("Forecast test1.xlsm")
Ary = Array("Jas", "Bas", "Tas", "Bar")

For i = 0 To UBound(Ary)
Workbooks(Ary(i) & ".xlsm").Sheets("Jas").Range("D3:O369").Copy Wbk.Sheets(Ary(i)).Range("D3")
Next i
End Sub

 

[Kthom019]

I had changed the range also

'Workbooks("Userform 25.xlsm").Sheets("Jas").Range("D6:O360").Copy Workbooks("Copy Shapes.xlsm").Sheets("Jas-1").Range("D6")'

This was what I did.

'Workbooks("Userform 25.xlsm").Sheets("Jas").Range("A1:A30").Copy Workbooks("Copy Shapes.xlsm").Sheets("Jas-1").Range("D6")'

Remember, I am attempting to Copy data from workbook 'Jas' range D6:0360 into Workbook "Forecast - worksheet-'Jas-1', range D6:O360"

 

[My Aswer Is This]

Well I'm not sure what your saying.

Do you really have a Workbook named:

Userform 25.xlsm

I think not.

You need to change this to what your workbook is named.

If your workbook is named "Jas" then change my script.
​

 

[Fluff]

Are the sheets you are copying too called Jas, Bas etc, or Jas-1, Bas-1 etc?
the names of these sheets needs to be exact.

 

[Fluff]

If the sheets are Jas-1 etc, try

Sub Kthom019()
   Dim Ary As Variant
   Dim i As Long
   Dim wbk As Workbook
   
   Set wbk = Workbooks("Forecast test1.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) & "-1").Range("D3")
   Next i
End Sub

 

[Kthom019]

The names of the sheets I am copying to are the same (Jas-1, Bas-1 etc).

Are the sheets you are copying too called Jas, Bas etc, or Jas-1, Bas-1 etc?
the names of these sheets needs to be exact.