Copied data to another sheet how to change values of rows

[Patriot2879]

Hi, good afternoon. I have got the code below where I copy data from one sheet to another but in row B the data in this is 13 numbers but it comes up as +23555 for example how can I change the value to this to show as the 13 digit number? also in Row Q is the date as 15/05/2019 but when copied over it comes up as 43233 how can I change this to date format automatically in the code for when transferred? Hope you can help please.

With copySheet
    .Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "BE")).Copy
  End With
  
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

and the below is the whole code

Private Sub CommandButton2_Click()
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("New")
  Set pasteSheet = Worksheets("Combined")

Range("AL2", Range("AL2").End(xlDown)).Copy Range("B2")
Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
Range("G2", Range("G2").End(xlDown)).Copy Range("D2")
Range("AH2", Range("AH2").End(xlDown)).Copy Range("AI2")
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
    .Value = .Value
End With
With Range("E2:E" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
    .Value = .Value
End With
With Range("AJ2:AJ" & lr)
    .Formula = "=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
    .Value = .Value
    
With copySheet
    .Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "BE")).Copy
  End With
  
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
End With
End Sub

 

[jondavis1987]

I would put this code in after the paste and change the ranges to what you need

    Sheets("pasteSheet").Select
    Range("H16").Select
    Selection.NumberFormat = "0"
    Range("L16").Select
    Selection.NumberFormat = "d/m/yyyy"

 

[Patriot2879]

Hi thanks for that, how do I select the whole row is it like...

Range ("B2:B")

?

 

[jondavis1987]

[COLOR=#666666]activesheet[/COLOR][COLOR=#666600].[/COLOR][COLOR=#666666]range[/COLOR][COLOR=#666600]([/COLOR][COLOR=#4070A0]"B1"[/COLOR][COLOR=#666600]).[/COLOR][COLOR=#666666]entirerow[/COLOR][COLOR=#666600].[/COLOR][COLOR=#007020][B]select[/B][/COLOR]

 

[Patriot2879]

thank you I shall try this shortly thanks for the help

 

[jondavis1987]

But B is a column. So are you needing column B selected and not rows?

 

[jondavis1987]

[COLOR=#666666]activesheet[/COLOR][COLOR=#666600].[/COLOR][COLOR=#666666]range[/COLOR][COLOR=#666600]([/COLOR][COLOR=#4070A0]"B1"[/COLOR][COLOR=#666600]).[/COLOR][COLOR=#666666]entirecolumn[/COLOR][COLOR=#666600].[/COLOR][COLOR=#007020][B]select[/B][/COLOR]

This is what you need if you need column B selected

 

[Patriot2879]

Hi I need it from like B2 all the way down to B100 for example but It cant be specific as changes daily. Would that be last row or column? Thank you for your help.

 

[jondavis1987]

that would be the column one that i posted

[COLOR=#666666][FONT=monospace]activesheet[/FONT][/COLOR][COLOR=#666600][FONT=monospace].[/FONT][/COLOR][COLOR=#666666][FONT=monospace]range[/FONT][/COLOR][COLOR=#666600][FONT=monospace]([/FONT][/COLOR][COLOR=#4070A0][FONT=monospace]"B1"[/FONT][/COLOR][COLOR=#666600][FONT=monospace]).[/FONT][/COLOR][COLOR=#666666][FONT=monospace]entirecolumn[/FONT][/COLOR][COLOR=#666600][FONT=monospace].[/FONT][/COLOR][COLOR=#007020][FONT=monospace][B]select[/B][/FONT][/COLOR]

B is a column, 1 is a row.

 

[jondavis1987]

    Sheets("pasteSheet").Select
    [COLOR=#666666][FONT=Verdana]activesheet[/FONT][/COLOR][COLOR=#666600][FONT=Verdana].[/FONT][/COLOR][COLOR=#666666][FONT=Verdana]range[/FONT][/COLOR][COLOR=#666600][FONT=Verdana]([/FONT][/COLOR][COLOR=#4070A0][FONT=Verdana]"B1"[/FONT][/COLOR][COLOR=#666600][FONT=Verdana]).[/FONT][/COLOR][COLOR=#666666][FONT=Verdana]entirecolumn[/FONT][/COLOR][COLOR=#666600][FONT=Verdana].S[/FONT][/COLOR][COLOR=#007020][FONT=Verdana][B]elect[/B][/FONT][/COLOR]
    Selection.NumberFormat = "0"
    Range("L16").Select
    Selection.NumberFormat = "d/m/yyyy"