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

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?

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

x = Cells(1, 1).Value

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

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

Typo

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



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave" wrote in message
ups.com...
x = Cells(1, 1).Value

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

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

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

"Bob Phillips" wrote:

Typo

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



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave" wrote in message
ups.com...
x = Cells(1, 1).Value

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

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

If x is numeric, then maybe:

windows(format(x,"000") & "_....



Amethyst wrote:

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

"Bob Phillips" wrote:

Typo

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



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave" wrote in message
ups.com...
x = Cells(1, 1).Value

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





--

Dave Peterson

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

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

"Dave Peterson" wrote:

If x is numeric, then maybe:

windows(format(x,"000") & "_....



Amethyst wrote:

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

"Bob Phillips" wrote:

Typo

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



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave" wrote in message
ups.com...
x = Cells(1, 1).Value

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





--

Dave Peterson