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
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