Copying Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 28 Feb 2006 14:33 GMT

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?

Reply to this Message

Tom Ogilvy - 28 Feb 2006 15:11 GMT

See Chip Pearson's page on programming the VBE

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 131 lines]
> part of the first workbook. How can I copy that macro and have it become a
> module in the new workbook. Can someone help?

Reply to this Message

Rob - 28 Feb 2006 15:56 GMT

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?

> See Chip Pearson's page on programming the VBE
>
[quoted text clipped - 139 lines]
> a
> > module in the new workbook. Can someone help?

Reply to this Message

Tom Ogilvy - 28 Feb 2006 16:15 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 145 lines]
> > a
> > > module in the new workbook. Can someone help?

Reply to this Message