Appending/Importing data from multiple files.

[BoilerBob]

We have a budget template we will be sending to all managers. We would like to compile them into one master database once completed.

How should we create a macro to append columns A:Y of rows 10:22 and rows 30-38 from multiple files and possibly multiple tabs into one tab?

It would be also helpful to have cell A1 from the tabs (mgr name) to be placed in column Z for each entry.

I apologize if this has been answered before, but I was unsure which terms to search for.

 

[ravishankar]

Hi
Paste the following codes in the macro window ( alt F11)

Sub rrr()
Cells(3, 1).Select
F = Dir("D:\My Documents\" & "*.xls")
'will print name of all the xls files in that folder i
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select 'By row
F = Dir()
n = n + 1
Loop
d = 1
For e = 3 To 6
c = Cells(e, 1)
d = d + 1
Cells(d, 2) = Cells(e, 1)
Cells(d, 3) = "='D:\My Documents\[" & c & "]sheet1'!A1"
For a = 10 To 38
For b = 1 To 25
Cells(1, 1) = "='D:\My Documents\[" & c & "]sheet1'!" & Chr(b + 64) & a
Cells(d, b + 3) = Cells(1, 1)
Next b
d = d + 1
Next a
Next e
End Sub

Change file path from D:\My Documents\ to your file path. change sheet name from sheet1 to what you have (probably a manager's name?) Run the macro. It will collate sheet1 from all files in your folder from row 10 to 38 (does not skip in between) and from col A to y
If you are comfortable doing this, I will tailor it to your needs.
Ravi

 

[BoilerBob]

Thanks.

I tried to run a sample, and ended up with a "Run-time error '1004': Application-defined or object-defined error" with the following code:

Cells(d, 3) = "='C:\Budget\[" & c & "]sheet1'!A1" AFTER it pulled the data in my test file.

I think I can work around to clean up. Your help is appreciated!