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