Some of MyCell begin with the number 0

  • Hello,

    My problem seems trivial but I can't for the life of me find the solution. I am relatively new to VBA, though.

    I've set a range of cells on Sheet 1 as MyRange, with each cell set as MyCell.

    This range is variable in length. The code creates a sheet for each MyCell and names it with the name of MyCell's value.

    I'd like to then have the value of MyCell appear in Cell D1. It sounds so simple.

    The only problem is that some of MyCell begin with the number 0. When my code sets D1 as MyCell, it removes the 0 as it is recognizing it as a number.

    This is causing a problem as I'm using this cell to update a (later created) pivot table field and it will not work without the 0.

    Can someone please help? It would be greatly appreciated. Thank you!


    Code:


    Code
    1. Dim MyCell As Range, MyRange As Range
    2. Set MyRange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
    3. Set MyRange = Range(MyRange, MyRange.End(xlDown))
    4. For Each MyCell In MyRange
    5. Sheets.Add After:=Sheets(Sheets.Count)
    6. Sheets(Sheets.Count).Name = MyCell.Value
    7. Sheets(Sheets.Count).Range("D1").Value = MyCell.Value
    8. Next MyCell
  • There probably is a better way but you could format the Cell D1 to Text and the Zero will then be kept.


    Code
    1. Dim MyCell As Range, MyRange As Range
    2. Set MyRange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
    3. Set MyRange = Range(MyRange, MyRange.End(xlDown))
    4. For Each MyCell In MyRange
    5. Sheets.Add After:=Sheets(Sheets.Count)
    6. Sheets(Sheets.Count).Name = MyCell.Value
    7. Sheets(Sheets.Count).Range("D1").NumberFormat = "@"
    8. Sheets(Sheets.Count).Range("D1").Value = MyCell.Value
    9. Next MyCell