using a string variable for sheet name

[leetslla]

I am trying to use a variable for the name in the Sheets command so that I can put in the names of the CSV file that I have captured in a variable.

Here is the macro that I recorded using Excel 2007:

Please Login or Register  to view this content.

I would like to replace "Heil15 - Wood Stats" with a String variable called FileName1. Here is what I would like to ultimately get to:

Please Login or Register  to view this content.

I keep getting: run time error '9', subscript out of Range error. I have searched and searched but can't find an answer so that I can use a String variable in the code. Can somone (re)direct me?

Thanks.

[shg]

Welcome to the forum.

The error means that there is either no sheet named copyFrom in the active workbook, or no open workbook named copyTo.

[leetslla]

Welcome to the forum.

The error means that there is either no sheet named copyFrom in the active workbook, or no open workbook named copyTo.

I have assigned CopyTo and CopyFrom as variables. The value they have in the "Watch" window is "Heil15 - Wood Stats", but I get an error when I try to make whatever is inside of the ( ) of Sheets(whatever) a variable.

Is there any way to make the contents inside of Sheets.(whatever) a variable?

Right now typing

Please Login or Register  to view this content.

recognizes the sheet in the CSV file. When I say

Please Login or Register  to view this content.

and write

Please Login or Register  to view this content.

, I get the error.

Is there a solution to making the name of the sheet a variable and working as I want it to? Or is there a different way around the problem?

Thanks.

[shg]

Not

Please Login or Register  to view this content.

but

Sheets(CopyTo)

Post a workbook if you're still having a problem.

[leetslla]

SHG,

Thanks for your input because it helped me realize my error. I was referencing an active workbook that did not have the sheet that I was needing--as you stated. Once I referenced the correct workbook, then the variable name worked in Sheets(CopyTo)...