CODEWITHSUNDEEP
excelvba
spaindc
spaindc

Reputation: 361

Losing leading zero, how to keep number format

I have code creates new sheets naming them by their project numbers (e.g., PRJ_123456, PRJ_654654). The first chunk of code here works wonderfully. My problem is that some projects have 5 digits, instead of 6. In the second piece of code, when I pull the project number it causes problems for things I try to do. I have tried many ways of retaining the leading zero for five digit project numbers, but it does not do so when creating the spreadsheet.

 Sub Pop()
 Sheets("Project_Main").Select

  Range("C2").Select 
  Do Until IsEmpty(ActiveCell)

  If (Sheets("Program").Range("C3").Value) = ActiveCell.Value Then
        Dim PRJNumber
        PRJNumber = ActiveCell.Offset(0, 2)
        'PRJNumber.NumberFormat = "000000"
        MsgBox (PRJNumber)

        Sheets("Template_PRJ").Select
        Sheets("Template_PRJ").Copy Before:=Sheets(2) 
        Sheets("Template_PRJ (2)").Select

        Dim SheetPRJName
        SheetPRJName = "PRJ_" & PRJNumber
        Sheets("Template_PRJ (2)").Name = SheetPRJName 
  end if
  loop
  end sub

This is what I try to do later

For Each wks In ActiveWorkbook.Worksheets

If ((Left(wks.Name, 3) = "PRJ")) Then

    PRJNumber = Right(wks.Name, 6)
    'MsgBox (prjNumber)

....

Upvotes: 0

Views: 1189

Answers (1)

JNevill
JNevill

Reputation: 50034

  1. Make sure to DIM your PRJNumber variable as a string since that's what we are dealing with.
  2. When the value comes through, if it's 5 characters instead of 6 append a 0. After you assign PRJNumber use: If len(PRJNumber) = 5 then PRJNumber = "0" & PRJNumber

Since you are appending "PRJ_" on the front of it for the sheet name, then should be good to go from here.

Upvotes: 2

Related Questions