Copy values from several workbooks to one workbook

[LarsDyrby]

Could somebody help me with the code for copying values from 10 similar workbooks/worksheets to one central workbook/worksheet.

The 10 workbooks are named data1.xls, data2.xls ..... data10.xls. There are 2 worksheets in these workbooks - the one containing the values is named Data.

The values pasted must be entered in to the first available row in the central worksheet.

Thanks,
Lars

[XLGibbs]

There are several dozen such posts and samples within the forums, almost the same exact title...

Do a search to find many examples. This one seems appropriate to your scenario..
http://www.vbaexpress.com/forum/show...iple+workbooks

[LarsDyrby]

Thanks for the directions but I'm new to VBA and would like some additional help.

Thanks,
Lars

[XLGibbs]

[vba]Sub CombinefilesCopyDataSheet()

Dim Wkb1 As Workbook, wb As Workbook
Dim ws1 As Worksheet, WsA As Worksheet

Set Wkb1 = Workbooks("Destination.xls") 'change this to your desintation file name
Set ws1 = Wkb1.Sheets(1) '1st sheet in destination

For Each wb In Workbooks

If wb.Name <> "PERSONAL.XLS" And wb.Name <> Wkb1.Name Then
wb.Activate
Set WsA = wb.Sheets("Data")
'the below range is for the range of data on Sheets("Data") which goes to DESTINATION.xls
WsA.Range("A1:C200").Copy 'change this to your range
With ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With

Application.DisplayAlerts = False
wb.Close 'this closes the file just copied.
End If
Next wb

Wkb1.Activate



End Sub
[/vba]
This code is easily modified as noted. Open all of the other worksheets which have a "Data" sheet to copy from.

Assuming the you have the same size of data coming from each of those sheets, you can just specify the range where noted.

It will copy the range of data you specify to the workbook you specify(In my code it is "Destination.xls")
beginning in the 1st available row and continuing through the open workbooks.

Only have open the destination file (where this code would go) and the source files.

Maybe that will help you out?

[LarsDyrby]

Hi XLGibbs

Thanks for your reply.

Its not working. Error marked with red.

[vba]
Sub CombinefilesCopyDataSheet()

Dim Wkb1 As Workbook, Wkb2 As Workbook, wb As Workbook
Dim ws1 As Worksheet, WsA As Worksheet, WsB As Worksheet, wsD As Worksheet
Set Wkb1 = Workbooks("Master.xls") 'change this to your desintation file name
Set ws1 = Wkb1.Sheets(MasterData)
Set wsD = Wkb2.Sheets(Data)
counter = 1
For Each wb In Workbooks
If wb.Name <> "Master.xls" And wb.Name <> Wkb1.Name Then
wb.Activate
Set WsA = wb.Sheets("Data")
'the below range is for the range of data on Sheets("Data") which goes to Master.xls
WsA.Range("A2:I200").Copy 'change this to your range
With ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With

Application.DisplayAlerts = False
wb.Close 'this closes the file just copied.
End If
Next wb
Wkb1.Activate
End Sub
[/vba]

I've attached the files. Hope you can help me?



Thanks,
Lars

[mperrah]

I'm no guru, but do you need the path along with the file name.
Also try quotes around the sheet names

[vba]Set Wkb1 = Workbooks("Master.xls") 'change this to your desintation file name - c:\testfolder\Master.xls
Set ws1 = Wkb1.Sheets("MasterData")
Set wsD = Wkb2.Sheets("Data")
[/vba]

Hope this helps.
Mark

[figment]

[vba]
Dim Wkb1 As Workbook, Wkb2 As Workbook, wb As Workbook
Dim ws1 As Worksheet, WsA As Worksheet, WsB As Worksheet, wsD As Worksheet
Set Wkb1 = Workbooks("Master.xls") 'change this to your desintation file name
Set ws1 = Wkb1.Sheets(MasterData)
Set wsD = Wkb2.Sheets(Data)
[/vba]

he might still need a path but the current problem is that Wkb2 is not set to anything.

you need a line like:

Set Wkb1 = Workbooks("Master.xls")

but with Wkb2 instead of Wkb1.

also in the lines:

Set ws1 = Wkb1.Sheets(MasterData)
Set wsD = Wkb2.Sheets(Data)

both MasterData and Data need to be in quotes

[XLGibbs]

You didn't follow my directions to change only the points noted.

As stated in the code below, you only need to change those items marked (the destination file name, the range of data on the source wb sheet.)

Other things didn't need to be changed. Assuming that your destination file is called "Master.xls" and the sheet name you want the data pasted TO is called "MasterData" and assuming the SOURCE data in the other workbooks is in a sheet called "Data"

VBA:

Sub CombinefilesCopyDataSheet()

Dim Wkb1 As Workbook, wb As Workbook
Dim ws1 As Worksheet, WsA As Worksheet

Set Wkb1 = Workbooks("Master.xls") 'change this to your desintation file name
Set ws1 = Wkb1.Sheets("MasterData") '1st sheet in destination

For Each wb In Workbooks

If wb.Name <> "PERSONAL.XLS" And wb.Name <> Wkb1.Name Then
wb.Activate
Set WsA = wb.Sheets("Data")
'the below range is for the range of data on Sheets("Data") which goes to DESTINATION.xls
WsA.Range("A1:I200").Copy 'change this to your range
With ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With

Application.DisplayAlerts = False
wb.Close 'this closes the file just copied.
End If
Next wb

Wkb1.Activate



End Sub