VBA - Load multiple workbooks into multiple template in a workbook

[trexlim]

Greeting all,

I am checking if anyone have the below scenario as I have.

I am looking at sheet 2 of the many workbooks i have (similar format). I would like VBA to help me read from a folder and extract data from these sheet 2 and load into a master workbook.

The 2nd worksheet in the master workbook will have an empty template format (1st sheet is summary of behind data for my calculations). So the code will read number of workbooks (sheet 2 i mean) in the folder, it will copy the template and add sheets into the master workbook and start loading data from the many worksheets(sheet 2) into these templates.

1 sheet 2 into 1 template(worksheet in master workbook).

Any one has links or sets of code that do the similar?

Thanks in advance!

 

[ravishankar]

Create a file called A.xlsm and name a sheet as B. paste the following macro codes

Sub collate_excelfiles()
Dim a As Long, C As String
Dim f As String, C As String, x As Integer, y As Integer
Workbooks("A.xlsm").Sheets("B").Activate
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
f = Dir(Cells(1, 2) & "*.xls*")
Cells(2, 1).Select
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop


MsgBox "Listing is complete"
x = Sheets("B").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "there are " & x - 1 & " files"
For a = 2 To x
Workbooks("A.xlsm").Sheets("B").Activate
C = Cells(a, 1)
If Cells(a, 1) <> "A.xlsm" Then
Workbooks.Open Filename:=Cells(1, 2) & Cells(a, 1)
Sheets("sheet2").UsedRange.Copy
activeworkbook.close
Windows("A.xlsm").Activate
Sheets.Add.Name = C
Sheets(C).Range("A1").PasteSpecial
Z = Sheets("B").Cells(a, 1)
Windows(Z).Activate
End If
Next a

MsgBox "complete"


End Sub

save the file in the folder from which you need to extract data. on running macro, it lists files in column A, opens each file, copies sheet 2, creates a new sheet named the file name and pastes it there.
Ravi shankar

 

[trexlim]

I will have many workbooks in a folder , and 1 master workbook call Summary Format.xlsm.

In this Summary Format's sheet number 2 is the empty template format. VBA code to read for every workbooks in a folder and it will duplicate this template in Summary Format(1 workbook = 1 templates added after worksheet.count in the Summaryformat).

It will extract datas from these many workbook and paste into these templates.