Sheet Name as a Variable?
 

Hiya experts!!

I have recorded!!!! :rolleyes: (shame? naaah I have no shame!! :) )
macro that inserts new spreadsheets into the workbook and it just fail
to deliver because of the set name e.g. "Sheet2".

Could you please let me know how to make this new sheet a variable an
then via a msgBox enable the user to rename it? I guess it would b
something like that:

Sheets("VARIABLE").Name = "msgBox INPUT"

Many thanks!

--
Petitboeu
-----------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...fo&userid=1060
View this thread: http://www.excelforum.com/showthread.php?threadid=49517

Sheet Name as a Variable?
 

Dim SheetName As String
SheetName = InputBox("Enter Sheet Name")
If SheetName < "" Then CreateSheet SheetName
....

Public Sub CreateSheet(SheetName As String)
'
' if the sheet already exists select it
'
On Error GoTo CreateNewSheet
Sheets(SheetName).Select
Exit Sub
'
' if not create it
'
CreateNewSheet:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = SheetName
'
End Sub



"Petitboeuf" wrote:


Hiya experts!!

I have recorded!!!! :rolleyes: (shame? naaah I have no shame!! :) ) a
macro that inserts new spreadsheets into the workbook and it just fails
to deliver because of the set name e.g. "Sheet2".

Could you please let me know how to make this new sheet a variable and
then via a msgBox enable the user to rename it? I guess it would be
something like that:

Sheets("VARIABLE").Name = "msgBox INPUT"

Many thanks!!


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=495170

Sheet Name as a Variable?
 

thanks a lot! works a treat! :)

I didn't think this would cause a problem but... I don't seem to b
able to 'Select All' on my XL spreadsheet.
It either does a Cells.Select for then Copy but fails when hittin
it...

Any ideas? :cool

--
Petitboeu
-----------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...fo&userid=1060
View this thread: http://www.excelforum.com/showthread.php?threadid=49517