Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
I am attempting to trigger a macro within a macro. The code I am using limits
me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Try this line instead...........
Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Si, voy con Dios!
Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Sorry David...........
Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Thank you for your patience. What I think I need to add into my code is a way
to temporarily mark the pertinent worksheet the "active worksheet" so that the correct sheet is activated in order for the "Call PasteFromFinal" will work. I believe this isn't working because I need to tell it what sheet I want the macro to run on but the thing is the file name for that worksheet will vary (each file is save under our customer's last name, first name). Any suggestions? -- David P. "CLR" wrote: Sorry David........... Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Hi David.......
Maybe this....... Sheets("PertinentWorksheetName").Select Call PasteFromFinal If not, post your macro and explain what it does that you don't want it to, and/or what it don't do that you want it to do........ Vaya con Dios, Chuck, CABGx3 "David P." wrote: Thank you for your patience. What I think I need to add into my code is a way to temporarily mark the pertinent worksheet the "active worksheet" so that the correct sheet is activated in order for the "Call PasteFromFinal" will work. I believe this isn't working because I need to tell it what sheet I want the macro to run on but the thing is the file name for that worksheet will vary (each file is save under our customer's last name, first name). Any suggestions? -- David P. "CLR" wrote: Sorry David........... Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Hi David...........
Maybe this is what you are looking for.........with the "Pertinent Sheet" name in cell A1, this macro will take you to it, assuming it exists, and then run your "PasteFromFinal" macro..... Sub SwitchSheetToCellValue() Dim PertinentSheet PertinentSheet = Range("a1").Value Worksheets(PertinentSheet).Select Call PasteFromFinal End Sub hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Thank you for your patience. What I think I need to add into my code is a way to temporarily mark the pertinent worksheet the "active worksheet" so that the correct sheet is activated in order for the "Call PasteFromFinal" will work. I believe this isn't working because I need to tell it what sheet I want the macro to run on but the thing is the file name for that worksheet will vary (each file is save under our customer's last name, first name). Any suggestions? -- David P. "CLR" wrote: Sorry David........... Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Hello Chuck,
Glad you were able to come back to this. Your last suggestion may be what I'm looking for. Basically the code is done in a workbook called "Temporary" that all of my customer files paste info onto. You will notice that the last few steps involve copying cells from this workbook in preparation to paste them on the "pertinent file" that I have been referring to. What you have been helping me with is to devise a way to cause the macro go one extra step to paste that information onto the €śpertinent file€ť which will be named differently depending on the customer file I have open. Does that make more sense? If your last suggestion is what I'm looking for (Sub SwitchSheetToCellValue()) what part of your example might I change to try it? Please see the code below and thank you again. My macro: Sub CopyForFinalPaste() ' ' CopyForFinalPaste Macro ' Macro recorded 7/6/2005 by BLINDS ETC ' ' Sheets("Final").Select Range("A3:W288").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("43:282").Select Selection.Delete Shift:=xlUp Range("A3:W43").Select Selection.Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.Copy Application.DisplayAlerts = False Windows("Price Chart.xls").Activate ActiveWindow.Close Windows("Temporary.xls").Activate Application.DisplayAlerts = True ActiveWindow.WindowState = xlMinimized ActiveWindow.WindowState = xlMaximized ActiveWindow.Call PasteFromFinal End Sub -- David P. "CLR" wrote: Hi David........... Maybe this is what you are looking for.........with the "Pertinent Sheet" name in cell A1, this macro will take you to it, assuming it exists, and then run your "PasteFromFinal" macro..... Sub SwitchSheetToCellValue() Dim PertinentSheet PertinentSheet = Range("a1").Value Worksheets(PertinentSheet).Select Call PasteFromFinal End Sub hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Thank you for your patience. What I think I need to add into my code is a way to temporarily mark the pertinent worksheet the "active worksheet" so that the correct sheet is activated in order for the "Call PasteFromFinal" will work. I believe this isn't working because I need to tell it what sheet I want the macro to run on but the thing is the file name for that worksheet will vary (each file is save under our customer's last name, first name). Any suggestions? -- David P. "CLR" wrote: Sorry David........... Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run on the "Active Sheet"
Hi David............
Assuming your code does what you wish it to do, as far as copying the correct information and getting it ready to paste wherever, then paste it into this example where indicated, and enter the name of the file you wish to paste it to in cell A1, or change the a1 below to be whatever cell that info is already in.......... Sub SwitchSheetToCellValue() Dim PertinentSheet PertinentSheet = Range("a1").Value PASTE YOUR CODE HERE.............. Worksheets(PertinentSheet).Select Call PasteFromFinal End Sub hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Hello Chuck, Glad you were able to come back to this. Your last suggestion may be what I'm looking for. Basically the code is done in a workbook called "Temporary" that all of my customer files paste info onto. You will notice that the last few steps involve copying cells from this workbook in preparation to paste them on the "pertinent file" that I have been referring to. What you have been helping me with is to devise a way to cause the macro go one extra step to paste that information onto the €śpertinent file€ť which will be named differently depending on the customer file I have open. Does that make more sense? If your last suggestion is what I'm looking for (Sub SwitchSheetToCellValue()) what part of your example might I change to try it? Please see the code below and thank you again. My macro: Sub CopyForFinalPaste() ' ' CopyForFinalPaste Macro ' Macro recorded 7/6/2005 by BLINDS ETC ' ' Sheets("Final").Select Range("A3:W288").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("43:282").Select Selection.Delete Shift:=xlUp Range("A3:W43").Select Selection.Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.Copy Application.DisplayAlerts = False Windows("Price Chart.xls").Activate ActiveWindow.Close Windows("Temporary.xls").Activate Application.DisplayAlerts = True ActiveWindow.WindowState = xlMinimized ActiveWindow.WindowState = xlMaximized ActiveWindow.Call PasteFromFinal End Sub -- David P. "CLR" wrote: Hi David........... Maybe this is what you are looking for.........with the "Pertinent Sheet" name in cell A1, this macro will take you to it, assuming it exists, and then run your "PasteFromFinal" macro..... Sub SwitchSheetToCellValue() Dim PertinentSheet PertinentSheet = Range("a1").Value Worksheets(PertinentSheet).Select Call PasteFromFinal End Sub hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Thank you for your patience. What I think I need to add into my code is a way to temporarily mark the pertinent worksheet the "active worksheet" so that the correct sheet is activated in order for the "Call PasteFromFinal" will work. I believe this isn't working because I need to tell it what sheet I want the macro to run on but the thing is the file name for that worksheet will vary (each file is save under our customer's last name, first name). Any suggestions? -- David P. "CLR" wrote: Sorry David........... Just replace your line that reads........ Application.Run "'New Customer.xls'!PasteFromFinal" with this line........ Call PasteFromFinal and yes, there is a space between Call and PasteFromfinal You need only one entry of "Call PasteFromFinal" to run the "PasteFromFinal" macro, but you can do it as many times as you wish, like if you want to do other stuff in between times....... hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: Si, voy con Dios! Thank you Chuck. Revealing my novice level with Excel & Visual Basic, 1) Where in the code do I put "Call PasteFinal"? 2) Do I put a space between "Call" & "PasteFinal"? 3) Do I put "Call Paste Final" in more than 1 part of the code? -- David P. "CLR" wrote: Try this line instead........... Call PasteFromFinal hth Vaya con Dios, Chuck, CABGx3 "David P." wrote: I am attempting to trigger a macro within a macro. The code I am using limits me to the current file name as opposed to the "Active Sheet". The current file name in this case is "New Customer" but in the way that I want to use this macro it could be any file name, i.e. "Doe, John". In light of my elementary code below what can I change to allow this macro to trigger for the "Active Sheet" so that the file can be any name. Here is how the code reads now. Thanks: Sub RunPasteFinal() ' ' RunPasteFinal Macro ' Macro recorded 4/10/2006 by _ ' ' Application.Run "'New Customer.xls'!PasteFromFinal" End Sub -- David P. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |