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]

Now I understand, will try again.

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]

Did you see my modified code in post#19?

 

[Kthom019]

Yes thanks, I am trying to understand it to modify it. I did the below but I am still getting the message. I do not understand the second script.

Sub Consolidate_Actual_Files()


Dim Ary As Variant
Dim i As Long
Dim wbk As Workbook

Set wbk = Workbooks("Forecast test1.xlsm")
Ary = Array("JAS-1", "BAS-1", "TRAS-1", "BAR-1")

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

Did you see my modified code in post#19?

 

[My Aswer Is This]

It seems as if your having problems with my code and Fluff code.

One thing that is important to remember is that all sheet names and Workbook names must be exact with extensions.

And I just wrote a sample to show you how to copy One Workbooks range to another Workbooks range.

You must have the workbook name the sheet name and then the range just like in my example.

And all Workbooks must be open.

 

[Kthom019]

I understand this script, thank you very much

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.
​

 

[Kthom019]

Yes I did.

Yes thanks, I am trying to understand it to modify it. I did the below but I am still getting the message. I do not understand the second script.

Sub Consolidate_Actual_Files()


Dim Ary As Variant
Dim i As Long
Dim wbk As Workbook

Set wbk = Workbooks("Forecast test1.xlsm")
Ary = Array("JAS-1", "BAS-1", "TRAS-1", "BAR-1")

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

 

[Fluff]

Try changing this

Ary = Array("JAS-1", "BAS-1", "TRAS-1", "BAR-1")

to

   Ary = Array("JAS", "BAS", "TAS", "BAR")

 

[Kthom019]

Thank you both for assisting me. I gained understanding which was necessary. I then learnt that if the sheet I am copying from has linked formulas to another sheet or workbook, I can write the VBA to include copy paste special values. I also learnt how to add to the code to protect the sheet after updating. Your help was greatly appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback