formatting name in new worskeet VBA

[GedSalter]

I have a macro which doesn all that I need except with the new worksheet name. the name is relevant to a figure in the cell E9. The number in E9 is formatted as a four digit number so if 21 was typed in the cell reads 0021. Unfortunately when the new worksheet is created using this number it only creates the sheet as 21.

I require the sheet to be called 0021 in the tab. The numbers will change for each sheet created as Cell E9 value changes.

This is the current macro I have

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub AddSheet()
Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
ActiveSheet.Protect
End If
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] strPath = ActiveWorkbook.Path & "\Invoices"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & Range("E9"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End Sub[/FONT]

 

[Yongle]

try

wh.Name = Format(ws.Range("E9").Value,"0000")

 

[dmt32]

Hi,

Use the Range.Text property to return what you see in the cell & not its underlying value

change this line

wh.Name = ws.Range("E9").Value

to this

wh.Name = CStr(ws.Range("E9").Text)

and see if does what you want.

Dave

 

[GedSalter]

worked a treat, many thanks Yongle

 

[Yongle]

@GedSalter
I prefer solution suggested by @dmt32
- it is more generic and works regardless of the cell format