| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
make a macro 'worksheet specific'
i have used the macro recorder in excel 2007 to created several sheet
specific (page view) macros. however, when i run the macro from another page in the workbook, it shows those cells I have selected *from the current worksheet*. for example, if i run the Sales Table macro while in the documentation sheet, the macro previews the Sales Table cells (the ones i selected when creating the macro) but directly from the documentation sheet. obviously i am new to this, and i am NOT working with VB, lol. Thank you in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Hi Lori,
Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Thank you. Here is one of them:
' ' ViewTable Macro ' Created 10/28/2007. Displays Sales Table worksheet. ' ' Keyboard Shortcut: Ctrl+t ' Range("B3:I18").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "S A L E S T A B L E" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub "Niek Otten" wrote: Hi Lori, Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
despite my lack of knowledge re this, i did see the problem once i posted the
macro for you. now i don't know how to change the "active sheet" property, and searching the web I can't find answers either. including whether or not i should continue any 'correction' down the page. help! "Lori" wrote: Thank you. Here is one of them: ' ' ViewTable Macro ' Created 10/28/2007. Displays Sales Table worksheet. ' ' Keyboard Shortcut: Ctrl+t ' Range("B3:I18").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "S A L E S T A B L E" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub "Niek Otten" wrote: Hi Lori, Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Just select the Sales Table sheet at beginning of your code then it will be the
ActiveSheet Sub View_Table() Sheets("Sales Table").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" etc. etc. Note: you could strip out many of the unnecessary lines. Sub Print_Sales_Table() Sheets("Sales_Table").Select With ActiveSheet.PageSetup .PrintArea = "$B$3:$I$18" .CenterHeader = "S A L E S T A B L E" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments ' .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub Gord Dibben MS Excel MVP On Sun, 28 Oct 2007 10:44:01 -0700, lori wrote: despite my lack of knowledge re this, i did see the problem once i posted the macro for you. now i don't know how to change the "active sheet" property, and searching the web I can't find answers either. including whether or not i should continue any 'correction' down the page. help! "Lori" wrote: Thank you. Here is one of them: ' ' ViewTable Macro ' Created 10/28/2007. Displays Sales Table worksheet. ' ' Keyboard Shortcut: Ctrl+t ' Range("B3:I18").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "S A L E S T A B L E" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub "Niek Otten" wrote: Hi Lori, Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
And to piggyback on Gord's answer...
You don't even have to select the sheet to work on it: Sub Print_Sales_Table() With workSheets("Sales_Table").PageSetup .PrintArea = "$B$3:$I$18" .CenterHeader = "S A L E S T A B L E" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments ' .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With workSheets("Sales_Table").PrintPreview 'you can't select a cell on a sheet that's not active. 'so I deleted that line to select B3. End Sub Gord Dibben wrote: Just select the Sales Table sheet at beginning of your code then it will be the ActiveSheet Sub View_Table() Sheets("Sales Table").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" etc. etc. Note: you could strip out many of the unnecessary lines. Sub Print_Sales_Table() Sheets("Sales_Table").Select With ActiveSheet.PageSetup .PrintArea = "$B$3:$I$18" .CenterHeader = "S A L E S T A B L E" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments ' .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub Gord Dibben MS Excel MVP On Sun, 28 Oct 2007 10:44:01 -0700, lori wrote: despite my lack of knowledge re this, i did see the problem once i posted the macro for you. now i don't know how to change the "active sheet" property, and searching the web I can't find answers either. including whether or not i should continue any 'correction' down the page. help! "Lori" wrote: Thank you. Here is one of them: ' ' ViewTable Macro ' Created 10/28/2007. Displays Sales Table worksheet. ' ' Keyboard Shortcut: Ctrl+t ' Range("B3:I18").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "S A L E S T A B L E" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub "Niek Otten" wrote: Hi Lori, Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Thank you, I used this and it worked. I was relieved to see that I didn't
have to go through the entire piece and change things. :) Thanks! Lori "Gord Dibben" wrote: Just select the Sales Table sheet at beginning of your code then it will be the ActiveSheet Sub View_Table() Sheets("Sales Table").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" etc. etc. Note: you could strip out many of the unnecessary lines. Sub Print_Sales_Table() Sheets("Sales_Table").Select With ActiveSheet.PageSetup .PrintArea = "$B$3:$I$18" .CenterHeader = "S A L E S T A B L E" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments ' .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub Gord Dibben MS Excel MVP On Sun, 28 Oct 2007 10:44:01 -0700, lori wrote: despite my lack of knowledge re this, i did see the problem once i posted the macro for you. now i don't know how to change the "active sheet" property, and searching the web I can't find answers either. including whether or not i should continue any 'correction' down the page. help! "Lori" wrote: Thank you. Here is one of them: ' ' ViewTable Macro ' Created 10/28/2007. Displays Sales Table worksheet. ' ' Keyboard Shortcut: Ctrl+t ' Range("B3:I18").Select ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$3:$I$18" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "S A L E S T A B L E" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.SelectedSheets.PrintPreview Range("B3").Select End Sub "Niek Otten" wrote: Hi Lori, Post your macro -- Kind regards, Niek Otten Microsoft MVP - Excel "Lori" wrote in message ... |i have used the macro recorder in excel 2007 to created several sheet | specific (page view) macros. however, when i run the macro from another page | in the workbook, it shows those cells I have selected *from the current | worksheet*. | | for example, if i run the Sales Table macro while in the documentation | sheet, the macro previews the Sales Table cells (the ones i selected when | creating the macro) but directly from the documentation sheet. | | obviously i am new to this, and i am NOT working with VB, lol. Thank you in | advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I make Excel display a specific worksheet? | Excel Discussion (Misc queries) | |||
| Event macro that targets specific worksheet | Excel Discussion (Misc queries) | |||
| Help with a macro to open to a specific worksheet | Excel Worksheet Functions | |||
| macro to sort data in worksheet by specific date | Excel Discussion (Misc queries) | |||
| make a macro to move specific rows to another sheet | Excel Worksheet Functions | |||
Hybrid Mode
