Copying Macro

Rob · 02-28-2006, 10:40 AM

I have a macro in a workbook that I would like to copy to another workbook
using a macro. The following is the code that creates the new workbook and
worksheet.

Sub AllocationView()
'
' AllocationView Macro
' Macro recorded 1/16/2006 by RLloyd
'

'

Sheets("Buy Tool").Select
ActiveSheet.Unprotect
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
Range("BB6:BN6").Select
With Selection.Font
.ColorIndex = x1Automatic
Rows("2595:2595").Select
Selection.EntireRow.Hidden = False
Range("G2589:G2594").Select
Selection.EntireRow.Hidden = True
End With
Range("BH6:BO6").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
ActiveSheet.Protect
End With
ActiveWindow.SmallScroll ToRight:=-4
Range("BD6").Select
ActiveWindow.SmallScroll ToRight:=-4
Application.DisplayAlerts = False
Sheets("Color Summary").Visible = False
Sheets("Tiering Summary").Visible = False
Sheets("Detail Key").Visible = False
Sheets("Header Key").Visible = False
Sheets("Allocation View").Visible = True
Sheets("Allocation View").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireRow.Hidden = False
Columns("A:C").Select
Selection.EntireRow.Hidden = False
Range("b7:b2590").Select
Selection.SpecialCells(xlCellTypeFormulas, 1).Select
Selection.EntireRow.Hidden = True
Selection.EntireColumn.Hidden = True
Cells.Select
Selection.Copy
Sheets("Allocation View").Visible = False
Workbooks.Add
Cells.Select
ActiveSheet.Paste
ActiveWindow.Zoom = 75
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Allocation View"
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Columns("S:U").Select
Selection.EntireColumn.Hidden = True
Columns("AN:AO").Select
Selection.EntireColumn.Hidden = True
Columns("V:Y").Select
Selection.EntireColumn.Hidden = True
Range("G1").Select
ActiveSheet.Protect
Application.DisplayAlerts = True
Workbooks(1).Activate
Sheets("Allocation View").Visible = True
Sheets("Allocation View").Select
ActiveSheet.Protect
Sheets("Allocation View").Visible = False
Workbooks(2).Activate
Range("G1").Select
ActiveCell.FormulaR1C1 = "Allocation View"
Range("F7").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 40
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Allocation View"
ActiveSheet.Protect
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

This macro creates a command button that runs another macro called Visual
View. I would like to be able to click this button to run the Visual view
macro without having the first workbook open. I will somtimes need to save
this new Workbook and open it at a later date but the Visual View macro is a
part of the first workbook. How can I copy that macro and have it become a
module in the new workbook. Can someone help?

Tom Ogilvy · 02-28-2006, 11:15 AM

See Chip Pearson's page on programming the VBE

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

"Rob" <[email protected]> wrote in message
news:[email protected]...
> I have a macro in a workbook that I would like to copy to another workbook
> using a macro. The following is the code that creates the new workbook
and
> worksheet.
>
> Sub AllocationView()
> '
> ' AllocationView Macro
> ' Macro recorded 1/16/2006 by RLloyd
> '
>
> '
>
> Sheets("Buy Tool").Select
> ActiveSheet.Unprotect
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> End With
> Range("BB6:BN6").Select
> With Selection.Font
> .ColorIndex = x1Automatic
> Rows("2595:2595").Select
> Selection.EntireRow.Hidden = False
> Range("G2589:G2594").Select
> Selection.EntireRow.Hidden = True
> End With
> Range("BH6:BO6").Select
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> ActiveSheet.Protect
> End With
> ActiveWindow.SmallScroll ToRight:=-4
> Range("BD6").Select
> ActiveWindow.SmallScroll ToRight:=-4
> Application.DisplayAlerts = False
> Sheets("Color Summary").Visible = False
> Sheets("Tiering Summary").Visible = False
> Sheets("Detail Key").Visible = False
> Sheets("Header Key").Visible = False
> Sheets("Allocation View").Visible = True
> Sheets("Allocation View").Select
> ActiveSheet.Unprotect
> Cells.Select
> Selection.EntireRow.Hidden = False
> Columns("A:C").Select
> Selection.EntireRow.Hidden = False
> Range("b7:b2590").Select
> Selection.SpecialCells(xlCellTypeFormulas, 1).Select
> Selection.EntireRow.Hidden = True
> Selection.EntireColumn.Hidden = True
> Cells.Select
> Selection.Copy
> Sheets("Allocation View").Visible = False
> Workbooks.Add
> Cells.Select
> ActiveSheet.Paste
> ActiveWindow.Zoom = 75
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "Allocation View"
> Sheets("Sheet2").Select
> Application.CutCopyMode = False
> ActiveWindow.SelectedSheets.Delete
> Sheets("Sheet3").Select
> ActiveWindow.SelectedSheets.Delete
> Columns("S:U").Select
> Selection.EntireColumn.Hidden = True
> Columns("AN:AO").Select
> Selection.EntireColumn.Hidden = True
> Columns("V:Y").Select
> Selection.EntireColumn.Hidden = True
> Range("G1").Select
> ActiveSheet.Protect
> Application.DisplayAlerts = True
> Workbooks(1).Activate
> Sheets("Allocation View").Visible = True
> Sheets("Allocation View").Select
> ActiveSheet.Protect
> Sheets("Allocation View").Visible = False
> Workbooks(2).Activate
> Range("G1").Select
> ActiveCell.FormulaR1C1 = "Allocation View"
> Range("F7").Select
> ActiveWindow.FreezePanes = True
> With ActiveSheet.PageSetup
> .PrintTitleRows = "$1:$6"
> .PrintTitleColumns = ""
> End With
> With ActiveSheet.PageSetup
> .LeftHeader = ""
> .CenterHeader = ""
> .RightHeader = ""
> .LeftFooter = ""
> .CenterFooter = ""
> .RightFooter = ""
> .LeftMargin = Application.InchesToPoints(0.5)
> .RightMargin = Application.InchesToPoints(0.5)
> .TopMargin = Application.InchesToPoints(1)
> .BottomMargin = Application.InchesToPoints(1)
> .HeaderMargin = Application.InchesToPoints(0.5)
> .FooterMargin = Application.InchesToPoints(0.5)
> .PrintHeadings = False
> .PrintGridlines = False
> .PrintComments = xlPrintNoComments
> .CenterHorizontally = False
> .CenterVertically = False
> .Orientation = xlLandscape
> .Draft = False
> .PaperSize = xlPaperLegal
> .FirstPageNumber = xlAutomatic
> .Order = xlDownThenOver
> .BlackAndWhite = False
> .Zoom = 40
> End With
> Range("G1").Select
> ActiveCell.FormulaR1C1 = "Allocation View"
> ActiveSheet.Protect
> ActiveWorkbook.PrecisionAsDisplayed = False
>
>
> End Sub
>
>
>
> This macro creates a command button that runs another macro called Visual
> View. I would like to be able to click this button to run the Visual view
> macro without having the first workbook open. I will somtimes need to
save
> this new Workbook and open it at a later date but the Visual View macro is
a
> part of the first workbook. How can I copy that macro and have it become
a
> module in the new workbook. Can someone help?

Rob · 02-28-2006, 12:00 PM

Thanks, Tom, this really helps. Tell me, is there another site that would
help me create a pop up box that prompts for criteria. I want to create this
and have the data that is input into the popup box populate a cell in the
worksheet and then run a macro that selects the entire row that matches that
cell creating a new workbook book with those row. What do you think?

"Tom Ogilvy" wrote:

> See Chip Pearson's page on programming the VBE
>
> http://www.cpearson.com/excel/vbe.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Rob" <[email protected]> wrote in message
> news:[email protected]...
> > I have a macro in a workbook that I would like to copy to another workbook
> > using a macro. The following is the code that creates the new workbook
> and
> > worksheet.
> >
> > Sub AllocationView()
> > '
> > ' AllocationView Macro
> > ' Macro recorded 1/16/2006 by RLloyd
> > '
> >
> > '
> >
> > Sheets("Buy Tool").Select
> > ActiveSheet.Unprotect
> > With Application
> > .Calculation = xlManual
> > .MaxChange = 0.001
> > End With
> > Range("BB6:BN6").Select
> > With Selection.Font
> > .ColorIndex = x1Automatic
> > Rows("2595:2595").Select
> > Selection.EntireRow.Hidden = False
> > Range("G2589:G2594").Select
> > Selection.EntireRow.Hidden = True
> > End With
> > Range("BH6:BO6").Select
> > With Selection.Font
> > .Name = "Arial"
> > .FontStyle = "Bold"
> > .Size = 10
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = xlAutomatic
> > ActiveSheet.Protect
> > End With
> > ActiveWindow.SmallScroll ToRight:=-4
> > Range("BD6").Select
> > ActiveWindow.SmallScroll ToRight:=-4
> > Application.DisplayAlerts = False
> > Sheets("Color Summary").Visible = False
> > Sheets("Tiering Summary").Visible = False
> > Sheets("Detail Key").Visible = False
> > Sheets("Header Key").Visible = False
> > Sheets("Allocation View").Visible = True
> > Sheets("Allocation View").Select
> > ActiveSheet.Unprotect
> > Cells.Select
> > Selection.EntireRow.Hidden = False
> > Columns("A:C").Select
> > Selection.EntireRow.Hidden = False
> > Range("b7:b2590").Select
> > Selection.SpecialCells(xlCellTypeFormulas, 1).Select
> > Selection.EntireRow.Hidden = True
> > Selection.EntireColumn.Hidden = True
> > Cells.Select
> > Selection.Copy
> > Sheets("Allocation View").Visible = False
> > Workbooks.Add
> > Cells.Select
> > ActiveSheet.Paste
> > ActiveWindow.Zoom = 75
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Name = "Allocation View"
> > Sheets("Sheet2").Select
> > Application.CutCopyMode = False
> > ActiveWindow.SelectedSheets.Delete
> > Sheets("Sheet3").Select
> > ActiveWindow.SelectedSheets.Delete
> > Columns("S:U").Select
> > Selection.EntireColumn.Hidden = True
> > Columns("AN:AO").Select
> > Selection.EntireColumn.Hidden = True
> > Columns("V:Y").Select
> > Selection.EntireColumn.Hidden = True
> > Range("G1").Select
> > ActiveSheet.Protect
> > Application.DisplayAlerts = True
> > Workbooks(1).Activate
> > Sheets("Allocation View").Visible = True
> > Sheets("Allocation View").Select
> > ActiveSheet.Protect
> > Sheets("Allocation View").Visible = False
> > Workbooks(2).Activate
> > Range("G1").Select
> > ActiveCell.FormulaR1C1 = "Allocation View"
> > Range("F7").Select
> > ActiveWindow.FreezePanes = True
> > With ActiveSheet.PageSetup
> > .PrintTitleRows = "$1:$6"
> > .PrintTitleColumns = ""
> > End With
> > With ActiveSheet.PageSetup
> > .LeftHeader = ""
> > .CenterHeader = ""
> > .RightHeader = ""
> > .LeftFooter = ""
> > .CenterFooter = ""
> > .RightFooter = ""
> > .LeftMargin = Application.InchesToPoints(0.5)
> > .RightMargin = Application.InchesToPoints(0.5)
> > .TopMargin = Application.InchesToPoints(1)
> > .BottomMargin = Application.InchesToPoints(1)
> > .HeaderMargin = Application.InchesToPoints(0.5)
> > .FooterMargin = Application.InchesToPoints(0.5)
> > .PrintHeadings = False
> > .PrintGridlines = False
> > .PrintComments = xlPrintNoComments
> > .CenterHorizontally = False
> > .CenterVertically = False
> > .Orientation = xlLandscape
> > .Draft = False
> > .PaperSize = xlPaperLegal
> > .FirstPageNumber = xlAutomatic
> > .Order = xlDownThenOver
> > .BlackAndWhite = False
> > .Zoom = 40
> > End With
> > Range("G1").Select
> > ActiveCell.FormulaR1C1 = "Allocation View"
> > ActiveSheet.Protect
> > ActiveWorkbook.PrecisionAsDisplayed = False
> >
> >
> > End Sub
> >
> >
> >
> > This macro creates a command button that runs another macro called Visual
> > View. I would like to be able to click this button to run the Visual view
> > macro without having the first workbook open. I will somtimes need to
> save
> > this new Workbook and open it at a later date but the Visual View macro is
> a
> > part of the first workbook. How can I copy that macro and have it become
> a
> > module in the new workbook. Can someone help?
>
>
>

Tom Ogilvy · 02-28-2006, 12:20 PM

Dim rng as Range
set rng = Columns(1).Find(Inputbox("Enter value in column A to search for"))
if not rng is nothing then
rows(rng.row).EntireRow.Select
Else
msgbox "Not found"
End if

--
Regards,
Tom Ogilvy

"Rob" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Tom, this really helps. Tell me, is there another site that would
> help me create a pop up box that prompts for criteria. I want to create
this
> and have the data that is input into the popup box populate a cell in the
> worksheet and then run a macro that selects the entire row that matches
that
> cell creating a new workbook book with those row. What do you think?
>
>
> "Tom Ogilvy" wrote:
>
> > See Chip Pearson's page on programming the VBE
> >
> > http://www.cpearson.com/excel/vbe.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Rob" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a macro in a workbook that I would like to copy to another
workbook
> > > using a macro. The following is the code that creates the new
workbook
> > and
> > > worksheet.
> > >
> > > Sub AllocationView()
> > > '
> > > ' AllocationView Macro
> > > ' Macro recorded 1/16/2006 by RLloyd
> > > '
> > >
> > > '
> > >
> > > Sheets("Buy Tool").Select
> > > ActiveSheet.Unprotect
> > > With Application
> > > .Calculation = xlManual
> > > .MaxChange = 0.001
> > > End With
> > > Range("BB6:BN6").Select
> > > With Selection.Font
> > > .ColorIndex = x1Automatic
> > > Rows("2595:2595").Select
> > > Selection.EntireRow.Hidden = False
> > > Range("G2589:G2594").Select
> > > Selection.EntireRow.Hidden = True
> > > End With
> > > Range("BH6:BO6").Select
> > > With Selection.Font
> > > .Name = "Arial"
> > > .FontStyle = "Bold"
> > > .Size = 10
> > > .Strikethrough = False
> > > .Superscript = False
> > > .Subscript = False
> > > .OutlineFont = False
> > > .Shadow = False
> > > .Underline = xlUnderlineStyleNone
> > > .ColorIndex = xlAutomatic
> > > ActiveSheet.Protect
> > > End With
> > > ActiveWindow.SmallScroll ToRight:=-4
> > > Range("BD6").Select
> > > ActiveWindow.SmallScroll ToRight:=-4
> > > Application.DisplayAlerts = False
> > > Sheets("Color Summary").Visible = False
> > > Sheets("Tiering Summary").Visible = False
> > > Sheets("Detail Key").Visible = False
> > > Sheets("Header Key").Visible = False
> > > Sheets("Allocation View").Visible = True
> > > Sheets("Allocation View").Select
> > > ActiveSheet.Unprotect
> > > Cells.Select
> > > Selection.EntireRow.Hidden = False
> > > Columns("A:C").Select
> > > Selection.EntireRow.Hidden = False
> > > Range("b7:b2590").Select
> > > Selection.SpecialCells(xlCellTypeFormulas, 1).Select
> > > Selection.EntireRow.Hidden = True
> > > Selection.EntireColumn.Hidden = True
> > > Cells.Select
> > > Selection.Copy
> > > Sheets("Allocation View").Visible = False
> > > Workbooks.Add
> > > Cells.Select
> > > ActiveSheet.Paste
> > > ActiveWindow.Zoom = 75
> > > Sheets("Sheet1").Select
> > > Sheets("Sheet1").Name = "Allocation View"
> > > Sheets("Sheet2").Select
> > > Application.CutCopyMode = False
> > > ActiveWindow.SelectedSheets.Delete
> > > Sheets("Sheet3").Select
> > > ActiveWindow.SelectedSheets.Delete
> > > Columns("S:U").Select
> > > Selection.EntireColumn.Hidden = True
> > > Columns("AN:AO").Select
> > > Selection.EntireColumn.Hidden = True
> > > Columns("V:Y").Select
> > > Selection.EntireColumn.Hidden = True
> > > Range("G1").Select
> > > ActiveSheet.Protect
> > > Application.DisplayAlerts = True
> > > Workbooks(1).Activate
> > > Sheets("Allocation View").Visible = True
> > > Sheets("Allocation View").Select
> > > ActiveSheet.Protect
> > > Sheets("Allocation View").Visible = False
> > > Workbooks(2).Activate
> > > Range("G1").Select
> > > ActiveCell.FormulaR1C1 = "Allocation View"
> > > Range("F7").Select
> > > ActiveWindow.FreezePanes = True
> > > With ActiveSheet.PageSetup
> > > .PrintTitleRows = "$1:$6"
> > > .PrintTitleColumns = ""
> > > End With
> > > With ActiveSheet.PageSetup
> > > .LeftHeader = ""
> > > .CenterHeader = ""
> > > .RightHeader = ""
> > > .LeftFooter = ""
> > > .CenterFooter = ""
> > > .RightFooter = ""
> > > .LeftMargin = Application.InchesToPoints(0.5)
> > > .RightMargin = Application.InchesToPoints(0.5)
> > > .TopMargin = Application.InchesToPoints(1)
> > > .BottomMargin = Application.InchesToPoints(1)
> > > .HeaderMargin = Application.InchesToPoints(0.5)
> > > .FooterMargin = Application.InchesToPoints(0.5)
> > > .PrintHeadings = False
> > > .PrintGridlines = False
> > > .PrintComments = xlPrintNoComments
> > > .CenterHorizontally = False
> > > .CenterVertically = False
> > > .Orientation = xlLandscape
> > > .Draft = False
> > > .PaperSize = xlPaperLegal
> > > .FirstPageNumber = xlAutomatic
> > > .Order = xlDownThenOver
> > > .BlackAndWhite = False
> > > .Zoom = 40
> > > End With
> > > Range("G1").Select
> > > ActiveCell.FormulaR1C1 = "Allocation View"
> > > ActiveSheet.Protect
> > > ActiveWorkbook.PrecisionAsDisplayed = False
> > >
> > >
> > > End Sub
> > >
> > >
> > >
> > > This macro creates a command button that runs another macro called
Visual
> > > View. I would like to be able to click this button to run the Visual
view
> > > macro without having the first workbook open. I will somtimes need to
> > save
> > > this new Workbook and open it at a later date but the Visual View
macro is
> > a
> > > part of the first workbook. How can I copy that macro and have it
become
> > a
> > > module in the new workbook. Can someone help?
> >
> >
> >