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
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Seamus2626Author Commented:
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)
0
SteveCommented:
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
0
SteveCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Seamus2626Author Commented:
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
0
SteveCommented:
Above the 'Sub Merge' have you dimed the wsDestination?

This is above the sub so it is available to all subs within the module without having to be passed about.

so add the line:
Dim wsDestination As Worksheet
At the very top of the module.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Perfect, thanks Steve!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.