Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
I created a macro that I want to apply to multiple worksheets in a book.
When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Sub Work_on_Selected_Sheets()
Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
I'm fairly new to excel, how would I apply the code? Through VB?
Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Mel
You originally stated that you had created a macro that runs on one worksheet successfully. Insert the same code into the place where I have 'your code here Example of code that runs on selected worksheets.................... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="justme" Next ws End Sub A more simple construct......... Sub wsname() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Name = ws.Cells(1, 1).Value Next ws End Sub Post your macro and I'm sure we can resolve the issue. Gord On Mon, 15 Jan 2007 14:51:00 -0800, Mel wrote: I'm fairly new to excel, how would I apply the code? Through VB? Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Thanks, here is the macro:
Sub Insert() ' ' Insert Macro ' This is footer information. ' ' Keyboard Shortcut: Ctrl+Shift+B ' With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub "Gord Dibben" wrote: Mel You originally stated that you had created a macro that runs on one worksheet successfully. Insert the same code into the place where I have 'your code here Example of code that runs on selected worksheets.................... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="justme" Next ws End Sub A more simple construct......... Sub wsname() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Name = ws.Cells(1, 1).Value Next ws End Sub Post your macro and I'm sure we can resolve the issue. Gord On Mon, 15 Jan 2007 14:51:00 -0800, Mel wrote: I'm fairly new to excel, how would I apply the code? Through VB? Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Mel
Sub Insert() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets With ws.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next ws End Sub Gord On Tue, 16 Jan 2007 04:49:01 -0800, Mel wrote: Thanks, here is the macro: Sub Insert() ' ' Insert Macro ' This is footer information. ' ' Keyboard Shortcut: Ctrl+Shift+B ' With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub "Gord Dibben" wrote: Mel You originally stated that you had created a macro that runs on one worksheet successfully. Insert the same code into the place where I have 'your code here Example of code that runs on selected worksheets.................... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="justme" Next ws End Sub A more simple construct......... Sub wsname() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Name = ws.Cells(1, 1).Value Next ws End Sub Post your macro and I'm sure we can resolve the issue. Gord On Mon, 15 Jan 2007 14:51:00 -0800, Mel wrote: I'm fairly new to excel, how would I apply the code? Through VB? Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Gord, this works like a charm. Thank you very much.
"Gord Dibben" wrote: Mel Sub Insert() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets With ws.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next ws End Sub Gord On Tue, 16 Jan 2007 04:49:01 -0800, Mel wrote: Thanks, here is the macro: Sub Insert() ' ' Insert Macro ' This is footer information. ' ' Keyboard Shortcut: Ctrl+Shift+B ' With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub "Gord Dibben" wrote: Mel You originally stated that you had created a macro that runs on one worksheet successfully. Insert the same code into the place where I have 'your code here Example of code that runs on selected worksheets.................... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="justme" Next ws End Sub A more simple construct......... Sub wsname() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Name = ws.Cells(1, 1).Value Next ws End Sub Post your macro and I'm sure we can resolve the issue. Gord On Mon, 15 Jan 2007 14:51:00 -0800, Mel wrote: I'm fairly new to excel, how would I apply the code? Through VB? Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Glad to hear that.
Thanks for the feedback. Gord On Tue, 16 Jan 2007 11:35:01 -0800, Mel wrote: Gord, this works like a charm. Thank you very much. "Gord Dibben" wrote: Mel Sub Insert() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets With ws.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next ws End Sub Gord On Tue, 16 Jan 2007 04:49:01 -0800, Mel wrote: Thanks, here is the macro: Sub Insert() ' ' Insert Macro ' This is footer information. ' ' Keyboard Shortcut: Ctrl+Shift+B ' With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "CONFIDENTIAL: FOR OFFICE USE ONLY" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .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 = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub "Gord Dibben" wrote: Mel You originally stated that you had created a macro that runs on one worksheet successfully. Insert the same code into the place where I have 'your code here Example of code that runs on selected worksheets.................... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="justme" Next ws End Sub A more simple construct......... Sub wsname() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Name = ws.Cells(1, 1).Value Next ws End Sub Post your macro and I'm sure we can resolve the issue. Gord On Mon, 15 Jan 2007 14:51:00 -0800, Mel wrote: I'm fairly new to excel, how would I apply the code? Through VB? Thanks "Gord Dibben" wrote: Sub Work_on_Selected_Sheets() Dim ws as WorkSheet Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets 'your code here Next ws End Sub Gord Dibben MS Excel MVP On Mon, 15 Jan 2007 12:40:02 -0800, Mel wrote: I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO AND MULTIPLE WORKSHEETS
Mel wrote:
I created a macro that I want to apply to multiple worksheets in a book. When I click on one worksheet the macro runs successfully, when I selelct multiple worksheets by selecting a tab and holding the shift key, the macro does not work? Is there a certain way to select the worksheets in order for the macro to work? Thanks. Yes, use the either activate the various worksheets with a Worksheets("Myworksheet").Activate ....then your code command, or alternatively use the With Worksheets("Myworksheet") ....your code End with If the macro is not to run on every worksheets, you'll probably want to embed these in a loop, whose loop variable is the name of all the relevant worksheets, which you should set up with an array of the relevant names HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro referencing multiple worksheets | Excel Discussion (Misc queries) | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Copying data to multiple worksheets by Macro | Excel Discussion (Misc queries) | |||
Page Setup for multiple worksheets macro problem | Excel Discussion (Misc queries) | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) |