formatting name in new worskeet VBA

GedSalter

Board Regular
Joined
Apr 24, 2019
Messages
62
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]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
try

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
Hi,

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

change this line

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

to this


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

and see if does what you want.

Dave
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
@GedSalter
I prefer solution suggested by @dmt32 :cool:
- it is more generic and works regardless of the cell format
 

Forum statistics

Threads
1,141,122
Messages
5,704,431
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top