Macro Source is Changing

[dhstein]

I have a workbook called Inventory.xlsm. This has several macros which are
selected with a button. One of them is "PO" which creates a Purchase Order
based on items selected in the Inventory workbook. The button is assigned to
the macro Inventory.xlsm!PO

The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the
file and closes c:\mypath\NewPO.xlsx

This all works - the file is created successfully.
However, at this point all macro buttons in Inventory.xlsm have been
reassigned so that I can't run them. For example, the PO button is now
assigned to c:\mypath\NewPO.xlsx!PO.

Any ideas are appreciated

 

[JLatham]

Can we start by seeing the section of code that creates and saves the
NewPO.xlsx file?

 

[dhstein]

JLatham

1) Thanks for the response. I'm going to include the entire macro here.
But before you get too involved in tracking this down, I just created a Macro
to reassign all buttons which I will run after the "Create PO" macro and that
should resolve the problem. If you are intellectually curious, then please
go ahead and see what you can find - I would love to understand why this is
happening. Thanks.


Sub CreatePO()

Dim Calc As String


If Application.Calculation = xlCalculationManual Then
Calc = "Manual"
Else

Calc = "Automatic"

End If


Application.Calculation = xlCalculationManual

Dim SaveFile As String
Dim NewSheetName As String
Dim RangeToUse As String
Dim SortColumn As String
Dim SortRange As String
Dim Range1 As String
Dim Range2 As String
Dim Range3 As String

Application.Calculation = xlCalculationManual

SaveFile = "PO Template with Description"

SavePath = "C:\Program Files\"

If FolderExists("C:\DATA\DAVID\DEVELOPMENT") Then

SavePath = "C:\DATA\DAVID\DEVELOPMENT\Inventory Project\Excel\PO\"

Else

SavePath = SavePath & "01 Transaction Pro Importer 3.0\"

End If


RangeToUse = Range("BK6").Value ' Range in Inventory Sheet to Copy

' Set up all ranges to use

SortColumn = Range("BL5").Value
SortRange = Range("BL6").Value

Range1 = Range("BK9").Value
Range2 = Range("BK10").Value
Range3 = Range("BK11").Value

'MsgBox RangeToUse

Sheets("PO").Select

' Clear out old data
Range("AA2:AX500").ClearContents
Sheets("Inventory").Select

Range(RangeToUse).Select

' Copy ordering data from Inventory sheet to PO sheet

Selection.Copy
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("PO").Select

Range("AA2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

' Sort PO data on "Order" column - to eliminate blanks

Range(SortRange).Select
ActiveWorkbook.Worksheets("PO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PO").Sort.SortFields.Add
Key:=Range(SortColumn), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("PO").Sort
.SetRange Range(SortRange)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


' Sort PO data - using only lines with order values

Range(Range1).Select
ActiveWorkbook.Worksheets("PO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PO").Sort.SortFields.Add Key:=Range(Range3), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("PO").Sort
.SetRange Range(Range1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.Calculation = xlCalculationAutomatic

Range(Range2).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


NewSheetName = ActiveSheet.Name

Sheets(NewSheetName).Select

Application.CutCopyMode = False
Sheets(NewSheetName).Move



ActiveWorkbook.SaveAs Filename:= _
SavePath & SaveFile & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Range("A24").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Inventory").Select

Range("A1").Select

If Calc = "Manual" Then

Application.Calculation = xlCalculationManual

End If

End Sub

 

[Dave Peterson]

First, if you have procedures that are common to a bunch of workbooks, you may
find putting them in a separate workbook/addin a much better way to organize
them.

You could give the users a toolbar or menu item that allows them to run the
macros.

Another option would be to replace the buttons from the Forms toolbar with
commandbuttons from the control toolbox toolbar. The code behind these
commandbuttons live in the worksheet module and would get copied to the new
workbook along with the sheet.

You'll have to revise your code (slightly) if you want to do that.

Or you could just reassign the correct macro to the current workbook after you
copy the sheet.

With activesheet 'or newwks or what represents the newly copied sheet
.buttons("yourbuttonnamehere").onaction _
= "'" & .parent.name & "'!" & "whatevermacronamehere"
end with

If you have lots, you could even cycle through each button/object and replace
the name of the old workbook with the new workbook.

======
I'd use the addin approach. I wouldn't want to have to update a macro with a
minor change and not have some idea what needs updating.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Dave Peterson]

ps.

Even though I wrote this message thinking that you were saving a single sheet
into a new workbook, it still applies if you're doing a File|SaveAs.

But instead of just looping through the objects on one sheet, you may have to
loop through each sheet, then through all objects on each sheet.