how to prevent vba to convert the value type while copy and pasting?

Question

Blockquote

hello i am trying to copy and paste a text formatted column's values from sheet1 to sheet 2. However the value type automatically changed into number.

Dim wb As Workbook Dim src As Worksheet Dim tgt As Worksheet Dim lastRow As Variant

Set wb = ThisWorkbook
Set src = wb.Sheets("sheet1")
Set tgt = wb.Sheets("sheet2")

lastRow = src.Range("A" & src.Rows.Count).End(xlDown).Row



tgt.Range("E43:E" & lastRow).Value = src.Range("A5:A" & lastRow).Value

what i get with this code is

 1
 2
 11
 12

the data is formatted in sheet1 like this:

 00000001
 00000002
 00000011
 00000012

and i want the same thing in sheet 2

Answer 1

Remove .Value and you will get desired results

Example:

ThisWorkbook.Sheets("Sheet2").Range("B2") = ThisWorkbook.Sheets("Sheet1").Range("B2")

Edit: Try the below additional line

ThisWorkbook.Sheets("Sheet2").Range("A1") = ThisWorkbook.Sheets("Sheet1").Range("A1")
ThisWorkbook.Sheets("Sheet2").Range("A1").NumberFormat = ThisWorkbook.Sheets("Sheet1").Range("A1").NumberFormat

Answer 2

Use copy and it will copy the cell/s exactly

src.Range("A5:A" & lastRow).Copy tgt.Range("E43:E" & lastRow)

Answer 3

As mentioned, you need to reproduce the custom number format that forces the leading zeroes from the source.

You also need to reshape the target to the same size as the source. Using lastRow for both only works when they start with the same row (which your example does not).

Set wb = ThisWorkbook
Set src = wb.Sheets("sheet1")
Set tgt = wb.Sheets("sheet2")

with src

  lastRow = .Range("A" & .Rows.Count).End(XLUP).Row  '<~~ xlUp, not xlDown

  with .Range("A5:A" & lastRow)

    tgt.Range("E43").resize(.rows.count, .columns.count) = .Value2
    tgt.Range("E43").resize(.rows.count, .columns.count).numberformat = "00000000"

  end with

end with