Recall a variable referring to a worksheet name?

M

Mike

I am writing a macro with the intent that it can be used on any
workbook. The user selects the first cell in a column that needs to
be modified in this particular way, then executes the macro. Since I
don't know what the contents will be on the particular worksheet that
this macro will be run, I plan on creating a new worksheet, modifiying
the data here, then copying the modified data back to the original
location on the original worksheet (and then deleting my temporary
worksheet so that they never see it). Since I don't know what the
name of the user's worksheet will be, I assigned it to a variable.
But when I try to go back to that worksheet, referencing the variable,
I get an error (Run-time error '9': Subscript out of range). Below is
the pertinent code regarding the variable name.

Dim DataSheet As Worksheet

Set DataSheet = ActiveSheet '(I've also tried several other
variations)
..
..
..
Selection.Cut
Sheets.Add.Name = "Temp"
ActiveSheet.Paste
Sheets("DataSheet").Select '(error occurs here)


Any help would be greatly appreciated.

Mike
 
P

Peter Atherton

I get an error (Run-time error '9': Subscript out of
Click to expand...
range). code regarding the variable name.
Dim DataSheet As Worksheet

Set DataSheet = ActiveSheet >Selection.Cut
Sheets.Add.Name = "Temp"
ActiveSheet.Paste
Sheets("DataSheet").Select '(error occurs here)
Click to expand...

Mike

try the following

Sub Test()
Dim DataSheet As Worksheet
Dim ns As Integer
ns = Sheets.Count

Set DataSheet = ActiveSheet '(I've also tried several
other
'variations)
DataSheet.Name = ActiveSheet.Name
Selection.Cut
Sheets.Add.Name = "Temp"
ActiveSheet.Paste
DataSheet.Select '(error occurs here)


End Sub


[email protected]

Regards
Peter
 
R

Rollin_Again

Change your code

SET DATASHEET = ACTIVESHEET[/B] SHOULD BE WRITTEN AS *DATASHEET
ACTIVESHEET.NAM


Also

Remove Quotation Marks from -----> Sheets("DataSheet").Select* s
it reads ----> *Sheets(DataSheet).Select*





Rolli
 
M

mudraker

You can use your variable in a couple of ways




Dim DataSheet as WorkSheet

set DataSheet = ActiveSheet
DataSheet.Select



or

Dim DataSheet as String
DataSheet = ActiveSheet.Name
Sheets(DataSheet).Selec
 
Top