ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   MACRO AND MULTIPLE WORKSHEETS (https://www.excelbanter.com/excel-worksheet-functions/126219-macro-multiple-worksheets.html)

Mel
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.

Gord Dibben
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.

Mel
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.

Gord Dibben
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.

Mel
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.

Gord Dibben
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.

Mel
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.

Gord Dibben
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.

Richard Buttrey
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


All times are GMT +1. The time now is 06:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com