Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
I have a workbook with 1 worksheet named "something_data" I would like to
add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
Got it
Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Mike" wrote: I have a workbook with 1 worksheet named "something_data" I would like to add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
Just as an aside you should probably have an error handler on that. If you
try to rename the sheet to the same name as an existing sheet then the code will crash in a most ungraceful fashion. -- HTH... Jim Thomlinson "Mike" wrote: Got it Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Mike" wrote: I have a workbook with 1 worksheet named "something_data" I would like to add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
Thanks for you reply. The sub will me in a template and there should be no
chance of another sheet but I will add an error handler anyway. One slightly related quest using the same sub. I want to copy range F1:F3 from strSheetName to A1:A3 on newSheetName. Can I do this without selecting the sheet? I will have several ranges like that I want to copy. Is there a way to newSheetName.Range("A1:A3") = strSheetName.Range("F1:F3") ?? Thanks Mike Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Jim Thomlinson" wrote: Just as an aside you should probably have an error handler on that. If you try to rename the sheet to the same name as an existing sheet then the code will crash in a most ungraceful fashion. -- HTH... Jim Thomlinson "Mike" wrote: Got it Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Mike" wrote: I have a workbook with 1 worksheet named "something_data" I would like to add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
You can do darn near everything without selecting. It is easy especially if
you become familiar with the following object. Workbook, Worksheet and Range. Workbook is not terribly applicable in this case but the other two are... Dim wksFromSheet As worksheet Dim wksNewSheet as worksheet Dim rngCopyFrom as Range Dim rngCopyTo as Range set wksFromSheet = activesheet Sheets.Add Type:="Worksheet" set wksNewSheet = activesheet wksNewSheet.Name = Replace(wksFromSheet.name, "_data", "_rpt") set rngcopyfrom = wksFromSheet.range("A1:C10") set rngcopyto = wksNewSheet.range("A1") rngcopyfrom.copy rngcopyto set rngcopyfrom = wksFromSheet.range("A30:C100") set rngcopyto = wksNewSheet.range("A15") rngcopyfrom.copy rngcopyto set wksFromSheet = nothing set wksNewSheet = nothing set rngCopyFrom = nothing set rngCopyTo = nothing End Sub You could avoid the range objects in this case if you really wanted to... -- HTH... Jim Thomlinson "Mike" wrote: Thanks for you reply. The sub will me in a template and there should be no chance of another sheet but I will add an error handler anyway. One slightly related quest using the same sub. I want to copy range F1:F3 from strSheetName to A1:A3 on newSheetName. Can I do this without selecting the sheet? I will have several ranges like that I want to copy. Is there a way to newSheetName.Range("A1:A3") = strSheetName.Range("F1:F3") ?? Thanks Mike Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Jim Thomlinson" wrote: Just as an aside you should probably have an error handler on that. If you try to rename the sheet to the same name as an existing sheet then the code will crash in a most ungraceful fashion. -- HTH... Jim Thomlinson "Mike" wrote: Got it Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Mike" wrote: I have a workbook with 1 worksheet named "something_data" I would like to add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
new worksheet name
Thanks so much for your help and suggestions.
Mike "Jim Thomlinson" wrote: You can do darn near everything without selecting. It is easy especially if you become familiar with the following object. Workbook, Worksheet and Range. Workbook is not terribly applicable in this case but the other two are... Dim wksFromSheet As worksheet Dim wksNewSheet as worksheet Dim rngCopyFrom as Range Dim rngCopyTo as Range set wksFromSheet = activesheet Sheets.Add Type:="Worksheet" set wksNewSheet = activesheet wksNewSheet.Name = Replace(wksFromSheet.name, "_data", "_rpt") set rngcopyfrom = wksFromSheet.range("A1:C10") set rngcopyto = wksNewSheet.range("A1") rngcopyfrom.copy rngcopyto set rngcopyfrom = wksFromSheet.range("A30:C100") set rngcopyto = wksNewSheet.range("A15") rngcopyfrom.copy rngcopyto set wksFromSheet = nothing set wksNewSheet = nothing set rngCopyFrom = nothing set rngCopyTo = nothing End Sub You could avoid the range objects in this case if you really wanted to... -- HTH... Jim Thomlinson "Mike" wrote: Thanks for you reply. The sub will me in a template and there should be no chance of another sheet but I will add an error handler anyway. One slightly related quest using the same sub. I want to copy range F1:F3 from strSheetName to A1:A3 on newSheetName. Can I do this without selecting the sheet? I will have several ranges like that I want to copy. Is there a way to newSheetName.Range("A1:A3") = strSheetName.Range("F1:F3") ?? Thanks Mike Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Jim Thomlinson" wrote: Just as an aside you should probably have an error handler on that. If you try to rename the sheet to the same name as an existing sheet then the code will crash in a most ungraceful fashion. -- HTH... Jim Thomlinson "Mike" wrote: Got it Sub CreateReport() Dim strSheetName As String newSheetName = ActiveSheet.Name Sheets.Add Type:="Worksheet" ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt") End Sub "Mike" wrote: I have a workbook with 1 worksheet named "something_data" I would like to add a new worksheet names "something_rpt" The "something" will come form another sub used earlier. I am trying this: Sub CreateReport() Dim strSheetName As String strSheetName = ActiveSheet.Name Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = Replace(strSheetName, "_data", "_rpt") End Sub but I get an error at this line - Sheets("Sheet1").Select saying subscript out of range. How do I add my sheet? Thanks Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |