Using a variable file name
 

Being a newbie, I apologize if this appears to be a trivial problem. I
tried looking at past postings but they don't seem to help much.

I am simply trying to copy and paste some stuff from one file to
another and my macro looks like:

Windows("C1.xls").Activate
Range("G12").Select
Selection.Copy
Windows("Standard Input Template.xls").Activate
Range("G12").Select
ActiveSheet.Paste

where C1.xls is the file I'm copying from and the macro belongs to the
file "Standard Input Template".

However I may need to copy from other files and a sheet in it with the
same name as the file name.

So I've set up by picking the file name and the sheet name from a cell
:
***********
Dim Sname As String
Dim IFname As String

'
'Enter Basic Information
Application.ScreenUpdating = False

Sname = Range("E11").Value
IFname = Sname & ".xls!" & Sname


Windows(" & IFname & ").Activate
Range("E12:E13").Select
Selection.Copy
Windows("Standard Input Template.xls").Activate
Range("E12").Select
ActiveSheet.Paste
***********
This does not work :((

Please help!!!

Thanks in advance
Utkarsh

Using a variable file name
 

Try

Windows(IFname).Activate

instead of

Windows(" & IFname & ").Activate

Rgds

Rog



-----Original Message-----
Being a newbie, I apologize if this appears to be a
trivial problem. I
tried looking at past postings but they don't seem to
help much.

I am simply trying to copy and paste some stuff from one
file to
another and my macro looks like:

Windows("C1.xls").Activate
Range("G12").Select
Selection.Copy
Windows("Standard Input Template.xls").Activate
Range("G12").Select
ActiveSheet.Paste

where C1.xls is the file I'm copying from and the macro
belongs to the
file "Standard Input Template".

However I may need to copy from other files and a sheet
in it with the
same name as the file name.

So I've set up by picking the file name and the sheet
name from a cell
:
***********
Dim Sname As String
Dim IFname As String

'
'Enter Basic Information
Application.ScreenUpdating = False

Sname = Range("E11").Value
IFname = Sname & ".xls!" & Sname


Windows(" & IFname & ").Activate
Range("E12:E13").Select
Selection.Copy
Windows("Standard Input Template.xls").Activate
Range("E12").Select
ActiveSheet.Paste
***********
This does not work :((

Please help!!!

Thanks in advance
Utkarsh
.

Using a variable file name
 

Thanks Rog for the help.

However when I run the code as suggested it gives me an error:

Runtime error '9': Subscript out of range.

What could be happening?

Thanks
Utkarsh



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Using a variable file name
 

It worked!!! A typo on my part was creating the problem.
Thank you so much, Rog!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!