Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Office 2007 acting differently--macro not available in this workbo
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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Office 2003 macro won't run in Office 2007 | Excel Programming | |||
XL 2007 acting out ? | Excel Discussion (Misc queries) | |||
Office 2007 compatible with Office 2000 macro programming | Excel Programming | |||
Excel 2007 formulas acting weird | Excel Discussion (Misc queries) | |||
EXCEL 2007 acting up | New Users to Excel |