VBA Code to consolidate sheets from multiple workbooks into one workbook

[d247]

Hi All,
I have Workbook 1, but I am trying to combine one sheet from Workbook 2 and one sheet from Workbook 3 into Workbook 1 with one simple macro..

Can anyone help me?

This is what I have so far which does one sheet from Workbook 2 fine, but how can I repeat the steps and also merge one sheet from Workbook 3 with the same macro?

Please Login or Register  to view this content.

[davesexcel]

Try this out adjust to suite.

Please Login or Register  to view this content.

http://www.davesexcel.com/vbacodes.htm#858298827

[d247]

Try this out adjust to suite.

Please Login or Register  to view this content.

http://www.davesexcel.com/vbacodes.htm#858299473

This works perfectly for 1 sheet from 1 workbook, how would I repeat the process to pull a 2nd sheet from a 2nd workbook (all in the same macro?)

[davesexcel]

Did you try it?
Do you want both sheets in the same worksheet in workbook 1?

[d247]

I want to be able to pull sheets from multiple workbooks into my original workbook (that contains that macro).
I tried it, and it pulls 1 sheet from 1 workbook into my original file - how would I repeat the process to pull another sheet from a different workbook...?

[davesexcel]

The code loops through the folder, it opens each workbook in the folder, does it's stuff, then closes the workbook and opens the next workbook.

[d247]

I'm getting a subscript out of range error..

[d247]

This works perfectly for 1 sheet from 1 workbook, how would I repeat the process to pull a 2nd sheet from a 2nd workbook (all in the same macro?)

Where it says CC-INFO - am I supposed to change that with my sheet name?

[davesexcel]

Did you change the folder names, and sheet names?

[d247]

Did you change the folder names, and sheet names?

This is what I put... Where do I put the two sheet names?
The workbooks are called Sales, and Marketing... The sheets inside are just Sales1, Marketing1 Respectively.

[CODE]]Sub LoopThroughFolder()
Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook, bk As Workbook
Dim Rws As Long, Rng As Range

Set Wb = ThisWorkbook
'change the address to suite
MyDir = "C:\Users\X\"
MyFile = Dir(MyDir & "*.xlsx") 'change file extension
ChDir MyDir

Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
Workbooks.Open (MyFile)
Set bk = ActiveWorkbook
Sheets("CC-INFO").Copy Before:=Wb.Sheets(1) 'use your sheet variable
bk.Close True

Application.DisplayAlerts = 1
MyFile = Dir()

Loop

End Sub[/CODE

[d247]

Did you change the folder names, and sheet names?

Any ideas?

[davesexcel]

I would do it this way.

Please Login or Register  to view this content.