Combing Workbooks

[GazMo]

I have 2 workbooks, Customer & Suppliers, both of which have numerou
worksheets named after the country of origin.

Is there a way of combing these workbooks so that in sheets with th
same name it puts the data underneath any existing data?

Please help ??

 

[Dave Peterson]

Maybe this will get you started:

Option Explicit
Sub testme02()

Dim CustWkbk As Workbook
Dim SuplWkbk As Workbook
Dim CombWkbk As Workbook

Dim wks As Worksheet
Dim newWks As Worksheet
Dim destCell As Range

Set CustWkbk = Workbooks("customer.xls")
Set SuplWkbk = Workbooks("supplier.xls")
Set CombWkbk = Workbooks.Add(1)
CombWkbk.Worksheets(1).Name = "deletemelater"

For Each wks In CustWkbk.Worksheets
With wks
Set newWks = CombWkbk.Worksheets.Add
newWks.Name = .Name
Set destCell = newWks.Range("a1")

.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
destCell.PasteSpecial Paste:=xlPasteValues

With newWks
Set destCell = .Range("a" _
& .Cells.SpecialCells(xlCellTypeLastCell).Row + 1)
End With

With SuplWkbk.Worksheets(.Name)
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
destCell.PasteSpecial Paste:=xlPasteValues
End With
End With
Next wks

Application.DisplayAlerts = False
CombWkbk.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True


End Sub

This assumes that each workbook has each country's worksheet.