thomaspatton
07-12-2007, 03:55 PM
I'm working on a project for work and I recorded a good macro. Don't hate me for recording:devil2:
The macro is in the current month's workbook and copies 7 pages in order from the current months workbook to a blank reporting workbook. The only problem I'm getting with this is having to go in and manually change the recorded macro to read the current month's workbook, since the macro was recorded on a blank workbook.
Here's my question:
Can I place a variable in the macro to read the name of the current month's workbook without having to go in and change it? That way a clean book can be created every month without changing any macro code. I need this to be dummy proof... ya know, government work.
Here's my [recorded] code:
I highlighted the parts I need to be "variables", meaning it should read the name of the current month workbook that the macro is contained in.
Sub Compile_Report()
' Compile_Report Macro
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Range("A4:AK63").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("F4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll Down:=-36
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Master Roster").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:C2").Select
Windows("Blank Student Workbook.xls").Activate
Range("A4:AK63").Select
ActiveWindow.SmallScroll Down:=-48
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("HazMat Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("HazMat Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("EPC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("EPC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("ALC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("ALC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:L1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Data").Select
End Sub
Any help that anyone can offer will be much appreciated. Even if it's just a "That can't be done n00b, stop recording macroes." post.
The macro is in the current month's workbook and copies 7 pages in order from the current months workbook to a blank reporting workbook. The only problem I'm getting with this is having to go in and manually change the recorded macro to read the current month's workbook, since the macro was recorded on a blank workbook.
Here's my question:
Can I place a variable in the macro to read the name of the current month's workbook without having to go in and change it? That way a clean book can be created every month without changing any macro code. I need this to be dummy proof... ya know, government work.
Here's my [recorded] code:
I highlighted the parts I need to be "variables", meaning it should read the name of the current month workbook that the macro is contained in.
Sub Compile_Report()
' Compile_Report Macro
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Range("A4:AK63").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("F4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll Down:=-36
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Master Roster").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:C2").Select
Windows("Blank Student Workbook.xls").Activate
Range("A4:AK63").Select
ActiveWindow.SmallScroll Down:=-48
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("HazMat Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("HazMat Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("EPC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("EPC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("ALC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("ALC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:L1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Data").Select
End Sub
Any help that anyone can offer will be much appreciated. Even if it's just a "That can't be done n00b, stop recording macroes." post.