Recorded Macro to Copy Format

L

Lilbit

I have created the following macro which works perfectly:
Sub CreateMnthlySFAReport()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "MDSummary"
Range("a1, c1, d1, e1, f1, h1, k1, l1, m1, n1, o1, p1, r1, s1, t1,
u1,v1, w1, aa1, ab1, ae1, af1, ag1, ah1, ai1, aj1,
ak1").EntireColumn.Delete
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Stage5"
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Stage4"
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Stage3"
Application.DisplayAlerts = False
Workbooks.Open Filename:="H:\TRWPDOCS\SFA\SFA Report Format.xls"
Cells.Select
Selection.Copy
Windows("SFA Working Sheets.xls").Activate
ActiveSheet.Paste
Sheets("Stage4").Select
Windows("SFA Report Format.xls").Activate
Sheets("Stage4").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("SFA Working Sheets.xls").Activate
ActiveSheet.Paste
Sheets("Stage5").Select
Windows("SFA Report Format.xls").Activate
Sheets("Stage5").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("SFA Working Sheets.xls").Activate
ActiveSheet.Paste
Range("A1").Select
Windows("SFA Report Format.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

I recorded the following macro to copy the page formatting from the
MDSummary worksheet to the others, but I keep getting an error message.
It works perfectly by itself, but I want to incorporate it in the
above-noted macro. I think I'm just not inserting it correctly. Help!!
Thanks!!
Sheets(Array("Stage3", "Stage4", "Stage5", "MDSummary")).Select
Sheets("MDSummary").Activate
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.3)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
 
C

CLR

You can run Macro2 from within Macro1 by inserting this line........

Application.Run "Macro2"

Of course replacing Macro2 with the name of the second macro


Vaya con Dios,
Chuck, CABGx3
 
C

CLR

Yes, that would be fine, or at other places "in between" steps would work
also, depending on when in the sequence of Macro1 you want Macro2 to
fire....

Vaya con Dios,
Chuck, CABGx3
 
L

L. Howard Kittle

Hi Lilbit,
Thanks, but where do I put that line? At the end before End Sub?

You would put the line CLR suggested at the point when you want it to run in
the first macro. So if you want to run the first macro and THEN the second,
yes it would be just above END SUB of first macro.

If you would want to run the second macro at a certain point in the first
macro, put the line there, knowing that after the second macro finished,
control will come back to that line and the rest of the first macro will
complete itself.

HTH
Regards,
Howard
 
P

Peo Sjoblom

Sub MyMacro1()
'do this
MyMacro2
'do that
End Sub


you should put the second macro where you want it in the code, if the first
macro do something code wise and after this particular code you want the
second macro, you put it there, if that is before the end sub fine, if not
put it somewhere else, only you would know where you want it to execute.
 

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