make a macro 'worksheet specific'

[Lori]

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.

 

[Niek Otten]

Hi Lori,

Post your macro

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|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.

 

[Lori]

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

 

[lori]

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!

 

[Gord Dibben]

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

 

[Dave Peterson]

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

 

[Lori]

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

 

[Lori]

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]

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.

 

[Gord Dibben]

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/vba/beyond_the_macro_recorder/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

 

[Gord Dibben]

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

 

[Dave Peterson]

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