VBA HELP - Need a piece of code that will Paste Special Formats to be added to my code and not sure how to do it - Excel 2013

[magnum5az]

Hello All,

I have code that copies a subtotal line from a main sheet then pastes it on everysheet in my workbook and it used to paste the formatting but now its only pasting the values/formulas and not including formats.

Here is the code with a comment on the line I need updated

Dim ws As Worksheet


Set subs = Range("Grandtotal")

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
  
  Select Case ws.Name
        Case "Combined Mapping", "FMV Mapping", "StartHere", "NOI", "Yardi Report", "NOI Summary", "NOI - Combined Property", "Cash Flow Summary", "Acquisitions", "Comparison", "Asset Mgrs", "FMV", "SP FMV"
            'Do Nothing
        Case Else
    
    With ws
        LR = .Range("J" & Rows.Count).End(xlUp).Row
        subs.Copy .Range("A" & LR + 1) '<------ This Line of code
        .Range("J" & LR + 1).Formula = "=SUM(J7:J" & LR & ")"
        .Range("L" & LR + 1).Formula = "=SUM(L7:L" & LR & ")"
        .Range("M" & LR + 1).Formula = "=SUM(M7:M" & LR & ")"
        .Range("N" & LR + 1).Formula = "=SUM(N7:N" & LR & ")"
        .Range("Q" & LR + 1).Formula = "=SUM(Q7:Q" & LR & ")"
        .Range("R" & LR + 1).Formula = "=SUM(R7:R" & LR & ")"
        .Range("S" & LR + 1).Formula = "=SUM(S7:S" & LR & ")"
        .Range("A" & LR + 1).Formula = "=(E" & LR & ")& "" Grand Total"""
        .Columns.AutoFit
        .Columns("I").ColumnWidth = 1.57
        .Columns("K").ColumnWidth = 1.57
        .Columns("P").ColumnWidth = 1.57
        .Columns("A").ColumnWidth = 5.29
        
        Application.CutCopyMode = False
    End With
    
    End Select

Next ws

Call PrintArea

Sheets("StartHere").Activate

MsgBox "Process Complete" & vbCr & _
        "New Report Copied to Tabs" & vbCr & _
        "New Print Areas Set"
        
End Sub

 

[Veritan]

You could try splitting that line into 2 lines, like this:

subs.Copy
Range("A" & LR + 1).PasteSpecial Paste:=xlPasteAll

 

[magnum5az]

Worked perfectly. I tried that but left the "." in front of "Range" and it errored out. Always nice when another set of eyes looks at the code.

Thanks!

You could try splitting that line into 2 lines, like this:

subs.Copy
Range("A" & LR + 1).PasteSpecial Paste:=xlPasteAll

 

[Veritan]

Lol, you're welcome, glad it was something simple