Macro to copy the 0 number

[Master_Viper]

Please help, macro to copy the zero values.
Lets say I have a value of 2858074961100 in a cell and i want to copy automatically the 0749611 in another sheet
because when I used the code like this Mid(sh1.Range("A12"), 5, 7) the zero will not copied.

Thanks for your help.

[mdmackillop]

Set the numberformat to the required length

Sub test()
Ln = 7
Set sh1 = ActiveSheet
x = Mid(sh1.Range("A12"), 5, Ln)
With Range("A14")
.Value = x
.NumberFormat = WorksheetFunction.Rept("0", Ln)
End With
End Sub

[Master_Viper]

Hello mdmackillop,

Here is thecomplete code, where do I insert the code that you provided in my code.


Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, a, b, c As String
Set sh1 = Sheets("Data_Entry")
If sh1.Range("S448") = "N" Or sh1.Range("S448") = "ONT" Or sh1.Range("S448") = "PON" Then
b = "3Spring"
Else: sh1.Range("S448") = "Nest3"
b = "4Spring"
End If
If sh1.Range("E448") = "2858097400100" Then
c = "PS100"
Else: sh1.Range("E448") = "2858041501100"
c = "PS60"
End If
a = Array(sh1.Range("E19").Value, sh1.Range("E9").Value, sh1.Range("E7").Value, Mid(sh1.Range("E7"), 10, 4), sh1.Range("M446").Value, sh1.Range("O9").Value, _
sh1.Range("E434").Value, Mid(sh1.Range("E434"), 5, 7), sh1.Range("S444").Value, sh1.Range("E440").Value, Mid(sh1.Range("E440"), 5, 7), _
sh1.Range("E448").Value, c, sh1.Range("S448").Value, b)
With Sheets("ER100_Activation_Configuration").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 21).Value = a
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mm-dd-yy")
End With
End Sub

[mdmackillop]

Can you use Text values?

.Resize(, 21).NumberFormat = "@"
.Resize(, 21).Value = a

[Master_Viper]

what do you mean?

[mdmackillop]

Try the code above. Numbers will appear as text values, preserving leading 0

[Master_Viper]

Still notworking,

Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, a, b, c As String
Ln = 7
Set sh1 = Sheets("Data_Entry")
x = Mid(sh1.Range("E431"), 5, Ln)
With Range("E431")
.Value = x
.NumberFormat = WorksheetFunction.Rept("0", Ln)
End With
Set sh1 = Sheets("Data_Entry")
If sh1.Range("S448") = "N" Or sh1.Range("S448") = "ONT" Or sh1.Range("S448") = "PON" Then
b = "3Spring"
Else: sh1.Range("S448") = "Nest3"
b = "4Spring"
End If
If sh1.Range("E448") = "2858097400100" Then
c = "PS100"
Else: sh1.Range("E448") = "2858041501100"
c = "PS60"
End If
a = Array(sh1.Range("E19").Value, sh1.Range("E9").Value, sh1.Range("E7").Value, Mid(sh1.Range("E7"), 10, 4), sh1.Range("M446").Value, sh1.Range("O9").Value, _
sh1.Range("E434").Value, Mid(sh1.Range("E434"), 5, 7), sh1.Range("S444").Value, sh1.Range("E440").Value, Mid(sh1.Range("E440"), 5, 7), _
sh1.Range("E448").Value, c, sh1.Range("S448").Value, b)
With Sheets("ER100_Activation_Configuration").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 21).Value = a
.Resize(, 21).NumberFormat = "@"
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mm-dd-yy")
End With
End Sub

[mdmackillop]

Still notworking,
Not helpful. Can you post a sample workbook

[Master_Viper]

Problem solved I format column to text instead of number..

Thanks anyway.