Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
*Edited
"Rita" wrote: 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 *OLD 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
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 -- Regards Roger Govier Rita wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
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! "Roger Govier" wrote: 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 -- Regards Roger Govier Rita wrote: 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 . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
I also found out that my code is in Module 2 if that helps.
"Rita" wrote: 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! "Roger Govier" wrote: 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 -- Regards Roger Govier Rita wrote: 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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
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. "Rita" wrote: I also found out that my code is in Module 2 if that helps. "Rita" wrote: 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! "Roger Govier" wrote: 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 -- Regards Roger Govier Rita wrote: 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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy of VB code to new spreadsheet
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 tabWindow sectionHide 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 tabUnhidePersonal 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\XLS TART Yours should be in a similar location but with Rita in the path as opposed to Roger -- Regards Roger Govier Rita wrote: 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. "Rita" wrote: I also found out that my code is in Module 2 if that helps. "Rita" wrote: 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! "Roger Govier" wrote: 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 -- Regards Roger Govier Rita wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and append spreadsheet changes to new spreadsheet | Excel Discussion (Misc queries) | |||
Code to Copy Data from One Spreadsheet To Another | Excel Discussion (Misc queries) | |||
How do I copy spreadsheet data as shown to another spreadsheet? | Excel Discussion (Misc queries) | |||
In Excel I want to copy text from spreadsheet to spreadsheet | Excel Worksheet Functions | |||
Copy code to a new spreadsheet | Excel Discussion (Misc queries) |