how to change assigned workbook name

[poker]

I am looking to change the workbook name so that it can be used all the time

 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

 

[HalfAce]

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]

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]

Well, for that part you could just amend this line of code from:

Windows("Fan Tracking - 2006__.xls").Activate

To:

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]

Thank you very much for your code and for the explanation. I will take your advice

 

[HalfAce]

You're very welcome.

I will take your advice

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.)