Copy of VB code to new spreadsheet

[Rita]

I am saving a spreadsheet with a new name. When I run the macro in the new
spreadsheet, it automatically opens up the old spreadsheet as well as doing
the macro. It doesn't transfer me to the new spreadsheet, it just opens it
up. This is driving me crazy! Here is my VB code, if it helps. Thanks to
anyone who can help me.

Sub PRINT3SAVE()
'Sub date1()
'
' date1 Macro
' Saves date created
'
Range("U45").Select
Selection.Copy
Range("W4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("U46").Select
Selection.Copy
Range("W39").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

' PRINT3SAVE Macro
' Prints 3 copies and saves file
'
ActiveWindow.SelectedSheets.PrintOut Copies:=3
Application.Dialogs(xlDialogSaveAs).Show

End Sub

 

[Rita]

*Edited

 

[Roger Govier]

Hi Rita

There is nothing in the code you show which would cause that to happen.
There must be other macros in the workbook, either in a sub called
Auto_open or in an Open event in the ThisWorkbook module.

Your existing code could be simplified to

Sub PRINT3SAVE()

Range("U45").Copy
Range("W4").PasteSpecial xlPasteValues
Range("U46").Copy
Range("W39").PasteSpecial xlPasteValues
Application.CutCopyMode = False

ActiveWindow.SelectedSheets.PrintOut Copies:=3
Application.Dialogs(xlDialogSaveAs).Show

End Sub

 

[Rita]

Thank you Roger. I found this code under Workbook in ThisWorkbook:
Private Sub Workbook_Open()
End Sub
I also found this code under Worksheet (I changed all 3 of them):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
I tried deactivating all of them, but it still opens up the old file.

I also used this main spreadsheet as basis to create a new, different
spreadsheet and the same thing is happening when I use the macro in the new
spreadsheet, the old one is opening up.

Thanks for any help you can give me. I am so frustrated!

 

[Rita]

I also found out that my code is in Module 2 if that helps.

 

[Rita]

Well, I found out what the problem was, now I just need to figure out how to
fix it. I am using the Quick Access Toolbar for my Macros. I didn't know
that when you assign an icon for a macro, it keeps the original file with it.
It doesn't know to change when you create a new file with the same macro.
This explains why the "old" file it being opened when I run the macro.

Any ideas other than creating icons for each file? I have a lot of files
with the same macros, so this could get crazy.

Thanks again.

 

[Roger Govier]

Hi Rita

Yes, that would explain it.
What you need to do is place the macro in a new workbook, and save it as
personal.xlsm in your XLSTART folder.
Before saving, tgo to the View tab>Window section>Hide

Personal.xlsm gets loaded automatically (but hidden) whenever you start
Excel, and all of the macros in that file are available to you. Your
icon on the QAT should then point to personal.xlsm!Printsave

If you want to modify your macro, or add any other macros, then you
would need to View tab>Unhide>Personal and make your changes, then hide
it again and save.

In my case the XLstart folder is located at
C:\Users\Roger\AppData\Roaming\Microsoft\Excel\XLSTART

Yours should be in a similar location but with Rita in the path as
opposed to Roger