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
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