| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referring to a variable cell on another sheet | Excel Worksheet Functions | |||
| Referring to a variable file name | Excel Discussion (Misc queries) | |||
| referring to other worksheet | Excel Worksheet Functions | |||
| Referring to a worksheet using variable | Excel Programming | |||
| Referring to a local or global variable or constant dynamically? | Excel Programming | |||
![Mike[_79_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
