PDA

View Full Version : Solved: paste to match destination formatting



CatDaddy
01-17-2012, 11:35 AM
right now i have a code that updates a summary sheet based on two sheets that precede it but when i paste the formatting on the summary sheet is replaced (no borders/coloring). I would like to paste to match destination formatting. any suggestions?

Sub Update()
Dim i As Integer
With ActiveWorkbook
Sheets(3).Activate
With ActiveSheet
i = .Cells(8, 200).End(xlToLeft).Offset(0, 1).Column
End With

Sheets(1).Range("B1").Copy
Sheets(3).Activate
Cells(1, i).Select
ActiveSheet.Paste
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets(1).Range("F5:F26").Copy
Sheets(3).Activate
Cells(3, i).Select
ActiveSheet.Paste
Sheets(1).Range("I5:I26").Copy
Sheets(3).Activate
Cells(3, i + 2).Select
ActiveSheet.Paste

Sheets(1).Range("F4").Copy
Sheets(3).Activate
Cells(31, i).Select
ActiveSheet.Paste
Sheets(1).Range("I4").Copy
Sheets(3).Activate
Cells(31, i + 2).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sheets(2).Range("F5:F27").Copy
Sheets(3).Activate
Cells(8, i + 1).Select
ActiveSheet.Paste

Sheets(2).Range("I5:I27").Copy
Sheets(3).Activate
Cells(8, i + 3).Select
ActiveSheet.Paste
Sheets(2).Range("F4").Copy
Sheets(3).Activate
Cells(31, i + 1).Select
ActiveSheet.Paste
Sheets(2).Range("I4").Copy
Sheets(3).Activate
Cells(31, i + 3).Select
ActiveSheet.Paste

End With
End Sub

mdmackillop
01-17-2012, 12:08 PM
Try PasteSpecial. Also, no need to Select.
Sheets(1).Range("B1").Copy
Sheets(3).Cells(1, i).PasteSpecial xlValues

CatDaddy
01-17-2012, 01:17 PM
thank you so much! good to know :)