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

If your file extension in xl2007 is .xlsx then it has no macro in it. It
would have to have a .xlsm file extension fo contain a macro.



"pickytweety" wrote in message
...
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

The ".xlsx" file is created when I copy some "post-macro" sheets (that the
macro creates) into a new book.

In other words, I click the macro, new sheets are created. The macro stops.
I copy the newly created sheets into a new book (this is where the .xlsx
comes from). I go back to the original file and try to run the macro to
create more sheets (for the next zone) but this time it gives me an error
saying it can't find the macro...but that's because it's looking for it in
the new book (even if it's closed), not the original file. Why it tries to
find the macro in a new book is what has got me baffled.

I've tried closing the new book and it still won't work. I tried saving the
original .xls file as an .xlsm to see if it made a difference and it doesn't.
If I try to run the macro twice using the button, the second time it can't
find the macro. It's so weird. Keep in mind that this has been running for
a year with no trouble, then we switch to 2007 and it won't work with the
button after the first zone.
Thanks,
PTweety


"JLGWhiz" wrote:

If your file extension in xl2007 is .xlsx then it has no macro in it. It
would have to have a .xlsm file extension fo contain a macro.



"pickytweety" wrote in message
...
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


.

I'll try to be clearer. You cannot call a macro from a .xlsx file. It has
to have been saved as a .xlsm file to retain the macro.



"pickytweety" wrote in message
...
The ".xlsx" file is created when I copy some "post-macro" sheets (that the
macro creates) into a new book.

In other words, I click the macro, new sheets are created. The macro
stops.
I copy the newly created sheets into a new book (this is where the .xlsx
comes from). I go back to the original file and try to run the macro to
create more sheets (for the next zone) but this time it gives me an error
saying it can't find the macro...but that's because it's looking for it in
the new book (even if it's closed), not the original file. Why it tries
to
find the macro in a new book is what has got me baffled.

I've tried closing the new book and it still won't work. I tried saving
the
original .xls file as an .xlsm to see if it made a difference and it
doesn't.
If I try to run the macro twice using the button, the second time it can't
find the macro. It's so weird. Keep in mind that this has been running
for
a year with no trouble, then we switch to 2007 and it won't work with the
button after the first zone.
Thanks,
PTweety


"JLGWhiz" wrote:

If your file extension in xl2007 is .xlsx then it has no macro in it. It
would have to have a .xlsm file extension fo contain a macro.



"pickytweety" wrote in message
...
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


.

Follow up: When your file were transferred from the older version into
xl2007, if they contained macros and were not saved as .xlsm file types,
then you could very well have lost your macros. You need to check with your
IT folks for back-up if the macros were lost.



"pickytweety" wrote in message
...
The ".xlsx" file is created when I copy some "post-macro" sheets (that the
macro creates) into a new book.

In other words, I click the macro, new sheets are created. The macro
stops.
I copy the newly created sheets into a new book (this is where the .xlsx
comes from). I go back to the original file and try to run the macro to
create more sheets (for the next zone) but this time it gives me an error
saying it can't find the macro...but that's because it's looking for it in
the new book (even if it's closed), not the original file. Why it tries
to
find the macro in a new book is what has got me baffled.

I've tried closing the new book and it still won't work. I tried saving
the
original .xls file as an .xlsm to see if it made a difference and it
doesn't.
If I try to run the macro twice using the button, the second time it can't
find the macro. It's so weird. Keep in mind that this has been running
for
a year with no trouble, then we switch to 2007 and it won't work with the
button after the first zone.
Thanks,
PTweety


"JLGWhiz" wrote:

If your file extension in xl2007 is .xlsx then it has no macro in it. It
would have to have a .xlsm file extension fo contain a macro.



"pickytweety" wrote in message
...
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


.

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

It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the button?
I used to just right click the button and there would be a menu item called
Assign Macro.
--
Thanks,
PTweety


"Dave Peterson" wrote:

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
.

With the button from the Control Toolbox, when you right click the button in
design mode, repeat: in design mode, you will be able to select view code
from the pop up menu. Click on View Code and it will open the proper code
window where you should see a two line of code similar to this:

Private Sub CommandButton1_ Click()

End Sub

Put your old macro between these two lines and remove the title line of the
old macro as well as the duplicate End Sub. Now when you click the button
the code will run from the object that you have the button on (i.e. Sheet or
UserForm)

As an alternative, If you want to leave your macro in the public module,
then just use the click event to call that macro as below.

Private Sub CommandButton1_Click()
'your macro name here
End Sub




"pickytweety" wrote in message
...
It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the
button?
I used to just right click the button and there would be a menu item
called
Assign Macro.
--
Thanks,
PTweety


"Dave Peterson" wrote:

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
.

Ok in Project Explorer, my macro is located in the Sheet2 rather than the
Module section. How will I know for the future if I should be putting code
in the sheet or in a module? Also, I want my button to say "Prepare Report"
rather than "CommandButton1", but I can't figure out how to change the text
on the button.
--
Thanks,
PTweety


"JLGWhiz" wrote:

With the button from the Control Toolbox, when you right click the button in
design mode, repeat: in design mode, you will be able to select view code
from the pop up menu. Click on View Code and it will open the proper code
window where you should see a two line of code similar to this:

Private Sub CommandButton1_ Click()

End Sub

Put your old macro between these two lines and remove the title line of the
old macro as well as the duplicate End Sub. Now when you click the button
the code will run from the object that you have the button on (i.e. Sheet or
UserForm)

As an alternative, If you want to leave your macro in the public module,
then just use the click event to call that macro as below.

Private Sub CommandButton1_Click()
'your macro name here
End Sub




"pickytweety" wrote in message
...
It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the
button?
I used to just right click the button and there would be a menu item
called
Assign Macro.
--
Thanks,
PTweety


"Dave Peterson" wrote:

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
.



.

I'm confuse about where this button is--and what type it is.

If it's on a worksheet (button replaced with a commandbutton), then just
doubleclick on that commandbutton and you'll see where to place the code.

If it's really on a toolbar, then the answer changes to something else.

I'd build and assign the macro in code.

pickytweety wrote:

It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the button?
I used to just right click the button and there would be a menu item called
Assign Macro.
--
Thanks,
PTweety

"Dave Peterson" wrote:

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
.


--

Dave Peterson

Hi Dave,
The button is on a worksheet called Locations. What's on my toolbar (QAT)
is the button-drawing icon. (I didn't realize there were even two button
icons for drawing buttons in Excel--one for Forms Control and one for Control
Toolbox.) So your message indicating there were two was really helpful.
When I changed the code as you suggested my original problem was
solved--THANKS!!! Now I have a new question--I want my button in the Excel
worksheet to read "Prepare Report" instead of "CommandButton1". I tried
right-clicking on it and various other things like I used to do, but can't
figure out how to change the text.

Also, from Excel I used to be able to click on the Developer tab, then
Macros to see a list of all macros in the file. Is it not there now because
I changed the "Sub" line to "Private Sub"? I do want the macro to show up
under a list of macros in Excel.
The code is sort of "hidden" in the VB Project Explorer now--especially if
you forget which sheet it's on. I liked having it in the Module in VB
Project Explorer.
--
Thanks,
PTweety


"Dave Peterson" wrote:

I'm confuse about where this button is--and what type it is.

If it's on a worksheet (button replaced with a commandbutton), then just
doubleclick on that commandbutton and you'll see where to place the code.

If it's really on a toolbar, then the answer changes to something else.

I'd build and assign the macro in code.

pickytweety wrote:

It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the button?
I used to just right click the button and there would be a menu item called
Assign Macro.
--
Thanks,
PTweety

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.

Show that control toolbox toolbar again.
Click on the design mode icon on that toolbar.
Rightclick on the commandbutton and choose Command Object and then choose Edit.

You could also change the caption by going into design mode and showing the
commandbutton's property window. Look for the Caption property and change it to
what you want.

Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you
wrote about). It's a good way to hide them from users.

But you can find the commandbutton on the worksheet and know what worksheet
module it's in, right?

pickytweety wrote:

Hi Dave,
The button is on a worksheet called Locations. What's on my toolbar (QAT)
is the button-drawing icon. (I didn't realize there were even two button
icons for drawing buttons in Excel--one for Forms Control and one for Control
Toolbox.) So your message indicating there were two was really helpful.
When I changed the code as you suggested my original problem was
solved--THANKS!!! Now I have a new question--I want my button in the Excel
worksheet to read "Prepare Report" instead of "CommandButton1". I tried
right-clicking on it and various other things like I used to do, but can't
figure out how to change the text.

Also, from Excel I used to be able to click on the Developer tab, then
Macros to see a list of all macros in the file. Is it not there now because
I changed the "Sub" line to "Private Sub"? I do want the macro to show up
under a list of macros in Excel.
The code is sort of "hidden" in the VB Project Explorer now--especially if
you forget which sheet it's on. I liked having it in the Module in VB
Project Explorer.
--
Thanks,
PTweety

"Dave Peterson" wrote:

I'm confuse about where this button is--and what type it is.

If it's on a worksheet (button replaced with a commandbutton), then just
doubleclick on that commandbutton and you'll see where to place the code.

If it's really on a toolbar, then the answer changes to something else.

I'd build and assign the macro in code.

pickytweety wrote:

It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the
"Button(Form Control)" from my tool bar, add the "Command Button (Active X
Control)" to my toolbar. Go back into the file, redraw my button. Revise
the code as you suggested. Then how do I "attach" the macro to the button?
I used to just right click the button and there would be a menu item called
Assign Macro.
--
Thanks,
PTweety

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson