how to change assigned workbook name

poker

Board Regular
Joined
Oct 2, 2005
Messages
74
I am looking to change the workbook name so that it can be used all the time
Code:
 Range("A2").FormulaR1C1 = _
        "=VLOOKUP(RC[1],'[Fan Tracking - 2006__.xls]Info'!R13C71:R27C72,2,FALSE)"
        Range("A2:A" & Range("B65536").End(xlUp).Row).FillDown

And this one also:

Range("A2:M" & LR).Copy
Windows("Fan Tracking - 2006__.xls").Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
poker,
I'm afraid your request isn't very clear to me.
Can you re-phrase it or explain in more detail what you want help with?
 

poker

Board Regular
Joined
Oct 2, 2005
Messages
74
I would like to recode the code i created so that the workbook name will be a generic name. As the code is written it will work for 06 year but not 07. My plan is to distribute through out our refineries and some people would not know how to edit the macro to change to the current year
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Well, for that part you could just amend this line of code from:
Code:
Windows("Fan Tracking - 2006__.xls").Activate
To:
Code:
Windows("Fan Tracking - " & Year(Date) & "__.xls").Activate
This will ensure it'll always refer to the year of the current date. (As seen by the
computer's clock.)

I'm afraid I don't know what you're looking for with the vlookup part of your question.

Also (because you said you want to make this compatable in the future), you might want
to think about not hard coding the last row of excel as 65536.
In the newest version (already out in Beta version) there are more than 1.1 million rows
so row 65536 won't get you anywhere near the bottom of the sheet.
A more flexable approach would be to code the line:
Range("A65536").End(xlUp)
as:
Cells(Rows.Count, "A").End(xlUp)
That way it'll start on the bottom row of the sheet no matter how many (or few) rows
there are.
 

poker

Board Regular
Joined
Oct 2, 2005
Messages
74
Thank you very much for your code and for the explanation. I will take your advice
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
You're very welcome.
I will take your advice
:eek: Now there's something I don't get to hear very often!
My wife & daughters don't and nobody at work does. - (They just keep coming back to
have the same things fixed over & over again.) :LOL:
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top