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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel 2016 VBA to consolidate data from several workbooks into one book
Now I understand, will try again.
Upvote
0
Excel Facts
Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
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
Upvote
0
My Aswer Is This
Well-known Member
- Joined
- Jul 5, 2014
- Messages
- 19,511
- Office Version
- 2021
- Platform
- Windows
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.
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.
Upvote
0
I understand this script, thank you very much
Upvote
0
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.
Upvote
0
Similar threads
- Question
- Question
