CODEWITHSUNDEEP
excelvba
hans smith
hans smith

Reputation: 9

How to modify code to keep destination cell formatting

I recently came into usage of the code below:

Dim sht1, sht2 As Worksheet

Set sht1 = Worksheets("Sheet1")
Set sht2 = Worksheets("Table")

sht1.Range("K10").Copy sht2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
sht1.Range("G15").Copy sht2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

End Sub

I was hoping if the community could advise how to modify it to allow the destination cell to keep its formatting.

Any help would be greatly appreciated. Thank you in advance.

Upvotes: 0

Views: 58

Answers (1)

BigBen
BigBen

Reputation: 49998

Just do a value transfer, no need for Copy.

Note that sht1 is actually a Variant if you do not specifically declare it As Worksheet.

Dim sht1 As Worksheet, sht2 As Worksheet

Set sht1 = Worksheets("Sheet1")
Set sht2 = Worksheets("Table")

With sh2
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = sh1.Range("K10").Value
    .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Value = sh1.Range("G15").Value
End With

Upvotes: 2

Related Questions