Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mel Mel is offline
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mel Mel is offline
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mel Mel is offline
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mel Mel is offline
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro referencing multiple worksheets JULZ Excel Discussion (Misc queries) 1 October 9th 06 07:46 PM
error when running cut & paste macro Otto Moehrbach Excel Worksheet Functions 4 August 9th 06 01:49 PM
Copying data to multiple worksheets by Macro lqfong Excel Discussion (Misc queries) 0 June 26th 06 03:57 AM
Page Setup for multiple worksheets macro problem KonaAl Excel Discussion (Misc queries) 3 January 7th 06 03:33 AM
CREATE MACRO TO COPY MULTIPLE WORKSHEETS Bewilderd jim Excel Discussion (Misc queries) 5 March 3rd 05 11:00 PM


All times are GMT +1. The time now is 09:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"