asked on

Add some copy code

Hi, i have a sub below, i need to add to more sheets to the tab "All", however i do not want their headings to transfer over and i need the first to append to the current data, and the next to append below the second data.

Basically im merging three tabs of data onto one tab called "All"

Thanks

Sub MergeTabs()

Sheets.Add.Name = "All"

Set Wss = Workbooks("CurrentMonth.xlsx").Worksheets("Outbound")
Set Wst = Workbooks("CurrentMonth.xlsx").Worksheets("All")

Wss.UsedRange.Copy Destination:=Wst.Cells(1, 1)

End Sub

Seamus2626

ASKER

So, i think i need my destination cells altered

Sub MergeTabs()

Sheets.Add.Name = "All"

Set OutB = Workbooks("CurrentMonth.xlsx").Worksheets("Outbound")
Set InB = Workbooks("CurrentMonth.xlsx").Worksheets("Inbound")
Set InC = Workbooks("CurrentMonth.xlsx").Worksheets("Incountry")
Set Wst = Workbooks("CurrentMonth.xlsx").Worksheets("All")

OutB.UsedRange.Copy Destination:=Wst.Cells(1, 1)
InB.UsedRange.Copy Destination:=Wst.Cells(1, 1)
InC.UsedRange.Copy Destination:=Wst.Cells(1, 1)

Steve

The attached file has the following code...

Dim wsDestination As Worksheet

Sub merge()

Set wsDestination = Sheets("ALL")

Call CopyFromSheet(Sheets("Outbound1"))
Call CopyFromSheet(Sheets("Outbound2"))
Call CopyFromSheet(Sheets("Outbound3"))

End Sub

Sub CopyFromSheet(wsSource As Worksheet)

Dim FromRange As Range
Set FromRange = wsSource.Range("A2:F" & wsSource.Range("A" & Rows.Count).End(xlUp).Row)

FromRange.Copy wsDestination.Range("A" & wsDestination.Range("A" & Rows.Count).End(xlUp).Row + 1)

End Sub

Open in new window

This should provide a direction to go in.
Any questions feel free to ask.
ATB
Steve.
C--Users-shall-Desktop-Example.xlsm

Steve

The below is a slight variation on the code to include creation of the sheet and addition of the header row:

Dim wsDestination As Worksheet

Sub merge()

On Error Resume Next
Application.DisplayAlerts = False
Sheets("ALL").Delete
Application.DisplayAlerts = True
On Error GoTo 0


Set wsDestination = Sheets.Add
wsDestination.Name = "ALL"
Sheets("Outbound1").Range("A1:F1").Copy Sheets("ALL").Range("A1:F1")

Call CopyFromSheet(Sheets("Outbound1"))
Call CopyFromSheet(Sheets("Outbound2"))
Call CopyFromSheet(Sheets("Outbound3"))

End Sub

Sub CopyFromSheet(wsSource As Worksheet)

Dim FromRange As Range
Set FromRange = wsSource.Range("A2:F" & wsSource.Range("A" & Rows.Count).End(xlUp).Row)

FromRange.Copy wsDestination.Range("A" & wsDestination.Range("A" & Rows.Count).End(xlUp).Row + 1)

End Sub

Open in new window

C--Users-shall-Desktop-Example.xlsm

Seamus2626

ASKER

Thanks Steve,

I have amended my code slightly and am getting the error "Object Required" on line

FromRange.Copy wsDestination.Range("A" & wsDestination.Range("A" & Rows.Count).End(xlUp).Row + 1)

It looks exactly the same as yours, can you see why i would get this error?

Thanks

-------------------------

Sub merge()

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Combined Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set wsDestination = Sheets.Add
wsDestination.Name = "Combined Data"
Sheets("Incountry").Range("A1:AT1").Copy Sheets("Combined Data").Range("A1:AT1")

Call CopyFromSheet(Sheets("Incountry"))
Call CopyFromSheet(Sheets("Inbound"))
Call CopyFromSheet(Sheets("Outbound"))

End Sub

Sub CopyFromSheet(wsSource As Worksheet)

Dim FromRange As Range
Set FromRange = wsSource.Range("A2:AT" & wsSource.Range("A" & Rows.Count).End(xlUp).Row)

FromRange.Copy wsDestination.Range("A" & wsDestination.Range("A" & Rows.Count).End(xlUp).Row + 1)

End Sub

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Perfect, thanks Steve!!