?
Solved

Add some copy code

Posted on 2014-07-16
6
Medium Priority
?
148 Views
Last Modified: 2014-07-16
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
0
Comment
Question by:Seamus2626
  • 3
  • 3
6 Comments
 

Author Comment

by:Seamus2626
ID: 40198937
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
 
LVL 24

Expert Comment

by:Steve
ID: 40198991
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
 
LVL 24

Expert Comment

by:Steve
ID: 40199007
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:Seamus2626
ID: 40199541
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 40199596
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
 

Author Closing Comment

by:Seamus2626
ID: 40199681
Perfect, thanks Steve!!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question