VBA: Copy from columns on multiple worksheets

G

Guest

I have a template (Outlook Template) that gets updated by different division
in our company. My only issue with the Macro is that I may not have updates
for every division every time therefore certain sheets that I have set up in
the macro may not exist. This will cause my macro to error out & not sure
how to handle to make it flexible.

Also, is there an easier way that to repeat the code for each sheet as I am
copying the same columns from each worksheet? Only difference is that each
sheet name needs to populate column A on the Gross Sales In worksheet and the
data needs to be copied into the same columns one after the other.

Still very new at this so appreciate any help. Thanks.

Example:
'Copy RETAIL Outlook into Template
Windows("Outlook Template.xls").Activate
Sheets("RETAIL").Select
Range("H11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("GROSS SALES IN").Select
Range("C4").Select
ActiveSheet.Paste

Sheets("RETAIL").Select
Range("C11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("GROSS SALES IN").Select
Range("B4").Select
ActiveSheet.Paste

Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = "RETAIL"
Selection.Copy
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
ActiveSheet.Paste

'Copy MAIL-ORDER Outlook into Template
Sheets("MAIL-ORDER").Select
Range("H11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("GROSS SALES IN").Select
Columns("C:C").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveSheet.Paste

Sheets("MAIL-ORDER").Select
Range("C11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("GROSS SALES IN").Select
Columns("B:B").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveSheet.Paste

Range("B11").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.Offset(0, -1)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Formula = "MAIL-ORDER"

Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = "MAIL-ORDER"
Selection.Copy
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
ActiveSheet.Paste
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top