I'm not quite sure what you're doing, but it sounds like the macro is assigned
to a button from the Forms toolbar--and excel thinks that the macro should be in
that other workbook.

There are differences between the buttons from the Forms toolbar and
commandbuttons from the Control toolbox toolbar.

One of the nice (in this case for you) is that the macro isn't assigned to the
commandbutton. The code is placed into the worksheet module that owns the
commandbutton--and the procedure would be named something like:

Private Sub CommandButton1_Click()

This code will always be "assigned" to that button in that sheet. And if you
move/copy the sheet, then the new sheet will have all the properties of the old
sheet--including a copy of its own macro.

But your code will have to change.

One of the biggest differences is that all those unqualified ranges will refer
to the object owning the code--in this case, the worksheet with the
commandbutton.

When the code is in a general module, the unqualified ranges will refer to the
activesheet.

I didn't test this very much (er, at all--but it did compile):

Option Explicit
Private Sub CommandButton1_Click()

Dim wksLoc As Worksheet
Dim wksTemp As Worksheet
Dim wksNew As Worksheet
Dim wksRight As Worksheet
Dim strLocation As Range
Dim strLoop As Range
Dim r As Range
Dim Store As Variant 'string or what????

Set wksLoc = Sheets("Locations")
Set wksTemp = Sheets("Template")
Set wksRight = Sheets("Right")

'Select the list of stores on Locations sheet
With wksLoc
Set strLoop = .Range("a2", .Range("a2").End(xlDown))
End With

'Grab print range
Sheets("Template").Activate
ActiveSheet.Calculate
Application.Goto reference:="print_area"
Set r = Selection

'Loop through each location (store)
For Each strLocation In strLoop
With wksTemp
.Range("A5").Value = strLocation
Store = .Range("a5").Value
End With

'Create new sheet for location (store)
wksTemp.Copy Befo=wksRight
Set wksNew = ActiveSheet

With wksNew
'Make print range
ActiveSheet.PageSetup.PrintArea = r.Address
'Name new worksheet and calc it
.Name = Trim(Store)
ActiveSheet.Calculate
'Replace formulas with Values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Next strLocation

'We now have store sheets inbetween sheets Left and Right, but we need to
'move the Dist Total
' and the Total Company sheet in.

With Worksheets("Dist Ttl")
.Calculate
.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
.Copy After:=Sheets("Left")
ActiveSheet.Name = "District Total"
End With

With Worksheets("District total")
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With

With Worksheets("Ttl Co")
.Calculate
.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
.Copy After:=Sheets("Left")
ActiveSheet.Name = "Total Company"
End With

With Worksheets("Total company")
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With

End Sub



pickytweety wrote:

Hi, this macro was running fine, until the conversion to Office 2007. In
the Locations worksheet I have a list of locations in Zone 1. A worksheet is
created for each store in Zone 1. I manually move the created worksheets
into another Book. Then I go back to the original file to run Zone 2. Only
now I'm getting an error:
Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be
available in this workbook.

Why is it looking to the new book for the macro? The macro is in the
original file. It does let me click on the Developer tab, Macros, Run to run
the macro, but why can't I just click the button anymore? Correction, why
can't I click the button a second time (it runs the first time but then
somehow gets hooked to the new file, so I can't click the button a second
time).

I've posted code below in case it helps.
--
Thanks,
PTweety

Sub PrepareReport()
'
' PrepareReport Macro
'
'

Dim wksLoc As Worksheet
Dim wksTemp As Worksheet
Dim wksNew As Worksheet
Dim wksRight As Worksheet
Dim strLocation As Range
Dim strLoop As Range
Dim r As Range

Set wksLoc = Sheets("Locations")
Set wksTemp = Sheets("Template")
Set wksRight = Sheets("Right")

'Select the list of stores on Locations sheet
With wksLoc
Set strLoop = .Range("a2", .Range("a2").End(xlDown))
End With

'Grab print range
Sheets("Template").Activate
ActiveSheet.Calculate
Application.Goto reference:="print_area"
Set r = Selection

'Loop through each location (store)
For Each strLocation In strLoop
With wksTemp
.Range("A5").Value = strLocation
store = .Range("a5").Value
End With

'Create new sheet for location (store)
wksTemp.Copy Befo=wksRight
Set wksNew = ActiveSheet

With wksNew
'Make print range
ActiveSheet.PageSetup.PrintArea = r.Address
'Name new worksheet and calc it
.Name = Trim(store)
ActiveSheet.Calculate
'Replace formulas with Values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Next strLocation

'We now have store sheets inbetween sheets Left and Right, but we need to
move the Dist Total
' and the Total Company sheet in.

Sheets("Dist Ttl").Activate
ActiveSheet.Calculate
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
ActiveSheet.Copy After:=Sheets("Left")
ActiveSheet.Name = "District Total"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select

Sheets("Ttl Co").Activate
ActiveSheet.Calculate
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
ActiveSheet.Copy After:=Sheets("Left")
ActiveSheet.Name = "Total Company"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select


End Sub

--
Thanks,
PTweety

--

Dave Peterson