Macro problem - it opens a worksheet

[DavidJ726]

Using the macro recorder, I made the following macro to hide several rows,
prints the spreadsheet as previously defined by the print area, and then
unhides the rows and selects cell A1. I then made it available on the menu
bar (forgot how I did that) but now, whenever I use the macro, as well as
running on the open spreadsheet, it opens up the spreadsheet that (I think)
I used to record it from. Why does it do that, and how do I keep it from
opening that worksheet?

Here's what it looks like;

Sub HideRows()
'
' HideRows Macro
' Macro recorded 8/29/2004 by David
'

'
Rows("7:70").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("6:71").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub

Thanks,

David...

 

[Dave Peterson]

It sounds like you assigned the button the macro that was contained in that
other workbook.

So when you click that button, xl has to open that other workbook to have access
to that macro.

Did you mean to have this macro available for only this one workbook?

If yes, then
tools|customize
Rightclick on that icon that runs the macro
choose assign macro
and point to the macro in the current workbook.

 

[Don Guillett]

try removing your selections and place the macro in your PERSONAL.xls or an
addin
Sub HideRows()
Rows("7:70").EntireRow.Hidden = True
activesheet.printout
Rows("6:71").EntireRow.Hidden = False
End Sub

 

[DavidJ726]

Hi Dave,

No, all I want/need is for it to run on any worksheet I have open at the
time without the original having to be opened.

This is somewhat interesting (to me)... I have two workbooks that are open.
When I follow your instructions the "Assign Macro" dialoge box is blank in
the Macro name: field. In the middle section, it shows
filename.xls!HideRows for the workbook that is not in the foreground, and on
the next line it just shows the name of the macro (which is HideRows). In
the bottom section it shows all open workbooks.

When I exit out of the customization screens and select the other workbook
from the pulldown Window menu, I see nearly the exact same thing. Only this
time, in the center section the macro is listed 1st instead of the inactive

However, regardless which workbook is active and I'm running the macro from
it, the original (an older file) opens up as a 3rd workbook.

Thanks,

David...

 

[DavidJ726]

Thanks Guys!

I ended up deleting the macro then simply started over by inserting a module
in VB. I cleaned it up to look a bit more like Don's response, made sure it
was properly referenced the way Dave indicated, and now everything works the
way it should now.

David...

 

[Dave Peterson]

Lots of people use a workbook with the name of personal.xls for this kind of
routine.

If you save that file into your XLStart folder, then each time excel starts,
this workbook will be opened--and all the macros will be available.

And most people save this personal.xls as a hidden workbook. Window|hide, then
close excel. When prompted to save personal.xls, make sure you say yes.

 

[DavidJ726]

Ahhh...

Thanks Dave,

I didn't see any (seemingly) relevant topics for Personal.xls at the MS KB.
This now makes sense.

David...

 

[Don Guillett]

Pls post your macro here so we can see your final solution.