Copy data from one sheet to another change formatting

[robrobet]

Hi,

here is what I trying to do,
example I have sheet 1 with data, I have formula in X1 which read the value in A1.
I copy the data from sheet 1, X1 to Sheet 2 A 1(Paste Value only)
Sheet 2 I have VLOOKUP formula and doesn't work because the value I post in A1

When I click A1 cells, it show little !mark, if I click the option convert to number my vlookup function work.
I like to have macro which convert all of the data to number.

 

[maabadi]

Try first code for Non-Decimal Numbers & Second For Decimal Numbers

Sub Pasting()
Sheet2.Range("A1", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value = CLng(Sheet1.Range("X1", Sheet1.Range("X" & Rows.Count).End(xlUp)).Value)
End Sub

Sub Pasting2()
Sheet2.Range("A1", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value = CDbl(Sheet1.Range("X1", Sheet1.Range("X" & Rows.Count).End(xlUp)).Value)
End Sub

 

[maabadi]

Or try this:

Sub Pasting()
Sheets("Sheet2").Range("A1:A" & Sheets("Sheet1").Range("X" & Rows.Count).End(xlUp).Row).Value = Sheets("Sheet1").Range("X1:X" & Sheets("Sheet1").Range("X" & Rows.Count).End(xlUp).Row).Value
End Sub

 

[robrobet]

thanks for your help.

 

[maabadi]

You're Welcome & Thanks for Feedback.