get part of a file name from a cell value for use in a macro

A

Amethyst

Following is my sample code:

Windows("sample_1205.xls").Activate
Range("A4:C20").Select
Selection.Copy
Windows("xxx_122005 worksheet.xls").Activate
Range("H4").Select
ActiveSheet.Paste
Range("H3").Select

this code activates the first file and copies a range of cells to the second
file where "xxx" is a 3 digit number that changes, depending on the value in
cell "A1" of "sheet 2" in the "xxx_122005 worksheet". This worksheet is
also where the macro is stored.
Is there a way to get the macro to read cell "a1" as part of the file name?
 
B

Bob Phillips

Typo

Windows(x & "_122005 worksheet.xls").Activate



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
A

Amethyst

tried that, it keeps stoping at " Windows(x & "_122005
worksheet.xls").Activate ", I put the x= line before the activation of the
first file because x's value is on sheet2 of the second file which is also
the active file but not the active sheet when the macro is run.
any more ideas.. appreciate your help

x = Sheets("sheet2").Cells(1, 1).Value
Windows("sample_1205.xls").Activate
Range("A4:C20").Select
Selection.Copy
Windows(x & "_122005 worksheet.xls").Activate
Range("H4").Select
ActiveSheet.Paste
Range("H3").Select
 
A

Amethyst

I figured out the problem, I needed Range("A1") instead of Cells(1, 1)...
It works now. I appreciate all your help. Thanks a Bunch...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top