Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Macro
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Macro
See Chip Pearson's page on programming the VBE
http://www.cpearson.com/excel/vbe.htm -- Regards, Tom Ogilvy "Rob" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Macro
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Macro
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for copying ? | Excel Discussion (Misc queries) | |||
copying with a Macro | Excel Worksheet Functions | |||
Copying with a macro | Excel Worksheet Functions | |||
Macro copying macro code | Excel Programming | |||
copying macro | Excel Programming |