changing a macro each time the file name changes

A

andrewc

How can I program a macro so that it will be automatically updated each time
I change a file name?
 
J

Joel

There isn't a single answer to thsi question because it is specific to your
macro. Most time youi can use THISWORKBOOK top specify the file where the
macro is located.

If you open workbooks up during a macro then set a variable to the new
workbook like this

set newbk = workbooks.open(filename:="book1.xls")

Then use newbk to reference the workbook like this

Data = newbk.sheets("Sheet1").Range("A1").Value
 
M

Mike H

Hi,

Why not write the filename to work on into a cell and have your macro pick
up that name from the worksheet cell. For example this returns the filename.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Mike
 
A

andrewc

Hello Joel,

Thanks for you reply. It was most helpful. However, to clarify, I will
include the code that I have in my macro.

The code that I want to automatically change is
"Copy 2 of Prototype SO & PO.xls", which is the name of the excel file

My situation is that the file "Copy 2 of Prototype SO & PO.xls" already
contains the macro below. When I use "File Save" to change the name of the
file to "My Car.xls", for example, the code of the macro unfortunately
remains "Copy 2 of Prototype SO & PO." in the new file called "My Car.xls" .
How do I change "Copy 2 of Prototype SO & PO.xls" to "My Car.xls"?

The macro is listed below for your perusal.



Thanks,
Andrew


'
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy 2 of Prototype SO & PO.xls").Activate
End Sub
 
A

andrewc

Hello Mike,

How would you incorporate the my macro code into the code that you sent me?
My macro is listed below.
By the way, "Copy 2 of Prototype SO & PO.xls" is the file name that needs to
change automatically.



Thanks!

Andrew
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy 2 of Prototype SO & PO.xls").Activate
End Sub




Mike H said:
Hi,

Why not write the filename to work on into a cell and have your macro pick
up that name from the worksheet cell. For example this returns the filename.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Mike

andrewc said:
How can I program a macro so that it will be automatically updated each time
I change a file name?
Click to expand...
Click to expand...
 
Top