| 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Thank you, Dave. I did use Gord's answer, but I did not condense it like
both of you have. I didn't want to play with VB in any detail yet, this is all new to me. I am wondering, was there a step in my book that I missed that I could have completed via the macro recorder, to make this work without typing in the code? This was part of our assignment and everyone was pretty quiet about it, except me, lol. Either they didn't check to see if it worked or they know something I don't! Also, I wanted to clean up those macro's with editing (too late now but I would like to know for future reference). I entered "Application.ScreenUpdating = False" after the top line (1st line, but before the worksheet specs that I used from Gord). Then at the end I followed up with the same, with "= True" before the End Sub statement. It froze my program and I had to close it. I subsequently deleted it! I could post this in another thread, please tell me if I should, unless you 2 or someone else can answer here? Thanks again, Lori "Dave Peterson" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
That printpreview stuff doesn't work nice with screenupdating = false. So if
you would have added application.screenupdating = true before the .printpreview, I bet it would have worked ok for you. But you did learn a lesson. Save your work often. Especially, before you run some code that may destroy your workbook or freeze excel. === You could have recorded another couple of steps. One to select a different sheet, then select the "Sales Table" sheet. Then you would have known that that "sales table" sheet was active. But I wouldn't recommend doing stuff like this. I'd recommend what you're doing (plus a little more). Use the macro recorder to get the syntax right, then come back to the code and tweak it to drop the .select's and .activates and the false steps that aren't really necessary (like Gord did). Yep, you'll feel more comfortible each time you do it. But someday (heck, even today), you'll look at the original recorded code and realize that it's a monster to update. There's not too much difference between your recorded code and Gord's code (this time). But someday, I bet there will be. Lori wrote: Thank you, Dave. I did use Gord's answer, but I did not condense it like both of you have. I didn't want to play with VB in any detail yet, this is all new to me. I am wondering, was there a step in my book that I missed that I could have completed via the macro recorder, to make this work without typing in the code? This was part of our assignment and everyone was pretty quiet about it, except me, lol. Either they didn't check to see if it worked or they know something I don't! Also, I wanted to clean up those macro's with editing (too late now but I would like to know for future reference). I entered "Application.ScreenUpdating = False" after the top line (1st line, but before the worksheet specs that I used from Gord). Then at the end I followed up with the same, with "= True" before the End Sub statement. It froze my program and I had to close it. I subsequently deleted it! I could post this in another thread, please tell me if I should, unless you 2 or someone else can answer here? Thanks again, Lori "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
If you used the macro recorder and it looks like you did, Excel throws in a lot
of redundant junk, especially when doing a print setup. Only through familiarity will you be able to know what is needed and not. I am still guilty of using a "select" when I don't have to at times, as Dave pointed out. Tushar Mehta has some info on his site in the section on "beyond the macro recorder" with good pointers on cleaning up the code that was created by the recorder. http://www.tushar-mehta.com/excel/vb...rder/index.htm The Application.ScreenUpdating = False should not have frozen your macro. Not needed with Dave's code because no sheet change takes place. Gord On Sun, 28 Oct 2007 14:26:01 -0700, Lori wrote: Thank you, Dave. I did use Gord's answer, but I did not condense it like both of you have. I didn't want to play with VB in any detail yet, this is all new to me. I am wondering, was there a step in my book that I missed that I could have completed via the macro recorder, to make this work without typing in the code? This was part of our assignment and everyone was pretty quiet about it, except me, lol. Either they didn't check to see if it worked or they know something I don't! Also, I wanted to clean up those macro's with editing (too late now but I would like to know for future reference). I entered "Application.ScreenUpdating = False" after the top line (1st line, but before the worksheet specs that I used from Gord). Then at the end I followed up with the same, with "= True" before the End Sub statement. It froze my program and I had to close it. I subsequently deleted it! I could post this in another thread, please tell me if I should, unless you 2 or someone else can answer here? Thanks again, Lori "Dave Peterson" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
Didn't know that Dave.
Thanks for pointing it out and I like the qualifier on the statement<g "There's not too much difference between your recorded code and Gord's code (this time)." Gord On Sun, 28 Oct 2007 17:11:28 -0500, Dave Peterson wrote: That printpreview stuff doesn't work nice with screenupdating = false. So if you would have added application.screenupdating = true before the .printpreview, I bet it would have worked ok for you. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
make a macro 'worksheet specific'
IIRC, I've been stung by that bug a couple of times.
Here's another one from John Walkenbach's site: http://j-walk.com/ss/excel/odd/odd11.htm Gord Dibben wrote: Didn't know that Dave. Thanks for pointing it out and I like the qualifier on the statement<g "There's not too much difference between your recorded code and Gord's code (this time)." Gord On Sun, 28 Oct 2007 17:11:28 -0500, Dave Peterson wrote: That printpreview stuff doesn't work nice with screenupdating = false. So if you would have added application.screenupdating = true before the .printpreview, I bet it would have worked ok for you. -- Dave Peterson |
| 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 | |||
Linear Mode
