new worksheet name

[Guest]

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

 

[Guest]

Got it
Sub CreateReport()

Dim strSheetName As String
newSheetName = ActiveSheet.Name
Sheets.Add Type:="Worksheet"
ActiveSheet.Name = Replace(strSheetName, "_data", "_rpt")

End Sub

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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...

 

[Guest]

Thanks so much for your help and suggestions.

Mike