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

New Member
Joined
Jun 21, 2017
Messages
16
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

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Veritan

Active Member
Joined
Jun 21, 2016
Messages
367
You could try splitting that line into 2 lines, like this:

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

magnum5az

New Member
Joined
Jun 21, 2017
Messages
16
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top