Need Macro to copy data from a single sheet from work books in the same directory.

[bird970]

Hello all,

I am new to VBA. I need a macro to look at all .xls files in a directory and combine the data to a single master sheet. I also need it to unhidde all the columns before copying the data before copying it to the master sheet. I tried the code below but I am just getting errors. I am using excel 2010.

[CODE] Sub Combine()


Fpath = "C:\Users\Documents\Service Partners Alliance\Robin Sing\consolidation"
Fname = Dir(FilePth & "*.xls")

Do While Fname <> ""
Workbooks.Open Fpath & Fname
Sheets("Detail - 05-2012").Copy After:=Workbooks("Master.xlsm").Sheets(Workbooks("Master.xlsm").Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop

End Sub [CODE]

Thanks in advance.

[tigeravatar]

bird970,

Welcome to the forum!
As a side note, the code tags didn't work becuase you're missing the slash "/" in the ending code tag. It should be [/CODE]

As to your query, as long as each workbook in that folder has a sheet named 'Detail - 05-2012' and that worksheet has headers in row 1, then this should work for you:

Please Login or Register  to view this content.

[bird970]

Thanks so much the query worked perfectly.

[tigeravatar]

You're very welcome