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