Macro to run on the "Active Sheet"

[Guest]

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

 

[Guest]

Try this line instead...........

Call PasteFromFinal


hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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