Copying values in a Macro

[WatfordKev]

I am copying values from one sheet to another using a Macro. The values come across OK, but I also want to copy the formatting of the cell. I.e. the sheet2 cell value may be a percentage, integer etc., which I also need to be copied to the sheet1 cell.

Example
sheet1.Range("B73") = sheet2.Range("K" & value)

In this scenario, the sheet2 value may be 20% but the sheet1 value ends up as 0.2

 

[GTO]

sheet2.Range("K" & value).Copy Destination:=sheet1.Range("B73")

 

[WatfordKev]

that is brilliant, thank you very much. By way of icing on the cake as it were, Is there any way to retain the visual formatting of the destination cell, i.e. only transfer the 'Number' formatting?

 

[GTO]

Try:

Sheet1.Range("A1").Copy
Sheet3.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

...changing ranges to suit. Just as FYI, you can get this by recording the steps, just make sure to save in This Workbook.

 

[Rick Rothstein]

You can also do what you want this way...

Sheet1.Range("B73").Value = Sheet2.Range("K" & value).Value
Sheet1.Range("B73").NumberFormat = Sheet2.Range("K" & value).NumberFormat

 

[WatfordKev]

Dear GTO & Rick!

that is brilliant, thank you so much!