Recall a variable referring to a worksheet name?
 

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

Recall a variable referring to a worksheet name?
 

Hi
just use
DataSheet.Select

--
Regards
Frank Kabel
Frankfurt, Germany


Mike wrote:
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

Recall a variable referring to a worksheet name?
 

I get an error (Run-time error '9': Subscript out of
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)

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




Regards
Peter

Recall a variable referring to a worksheet name?
 

Change your code

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


Also

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





Rolli

--
Message posted from http://www.ExcelForum.com

Recall a variable referring to a worksheet name?
 

Sheets(DataSheet.Name).Select
or
DataSheet.Select

--
George Nicholson

Remove 'Junk' from return address.

"Mike" wrote in message
om...
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

Recall a variable referring to a worksheet name?
 

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

--
Message posted from http://www.ExcelForum.com