Pull & Paste files from folder loop

[xcelnovice]

Hi, in the code below I am grabbing a workbook from a folder and pasting it to the target wb. It works right now, what I'd like to know is how can i go about having it pull other workbooks and pasting into the target wb. So after the macro pulls the first wb(JH A MIP COPY) & pastes it into the target, I'd like it to cycle through the same folder that JH A MIP is in and find the other wb's in that folder and paste them to seperate tabs. Hope this makes sense.


Sub CostMgmtUpdate() 'Name of my program
Dim wbTarget As Workbook 'Cost Management report where data will be pasted
Dim wbSource As Workbook 'BTD File where data is pulled from
'Open both workbooks
Set wbTarget = Workbooks.Open("C:\xxxxx\xxxxx\Desktop\JH CLIN 0005 November 2015 SLIN Budget (CMS ART) vs Actuals (Costpoint)-COPY.xlsm")
Set wbSource = Workbooks.Open("C:\xxxxx\xxxxx\Desktop\JH A MIP-COPY.xlsx")
'Activate Summary sheet & find = & replace with #
wbTarget.Sheets("Summary").Cells.Replace What:="=", Replacement:="#", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'Now transfer data from source to target
wbSource.Sheets("JH A MIP-COPY").Range("A:H").Copy
wbTarget.Sheets("AB").Range("A1").PasteSpecial

 

[Rijnsent]

Hi xcelnovice,

what about this answer by Ron de Bruin: Merge data from all workbooks in a folder

Koen