| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recorded Macro to Copy Format
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Recorded Macro to Copy Format
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 "Lilbit" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Recorded Macro to Copy Format
Thanks, but where do I put that line? At the end before End Sub?
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Recorded Macro to Copy Format
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 "CLR" wrote in message ... 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 "Lilbit" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Recorded Macro to Copy Format
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 "Lilbit" wrote in message lkaboutsoftware.com... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Recorded Macro to Copy Format
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. -- Regards, Peo Sjoblom "Lilbit" wrote in message lkaboutsoftware.com... Thanks, but where do I put that line? At the end before End Sub? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Format Macro Buttons? | Excel Discussion (Misc queries) | |||
| Macro - Copy - Email | Excel Worksheet Functions | |||
| How do I copy a cell (content AND format) from one worksheet to a. | Excel Worksheet Functions | |||
Linear Mode
