I have a report that I run several times a day and I need to format it in excel. I have got my VBA macro working almost right but I have a few things I cant figure out
I have pasted my macro with notes in the places I need help. I need to copy row one and paste it after every page break so that it ends up at the top of each page. I also need to fill K2 and L2 down to the last row after inserting the formula in K2
Code
Sub CYCLE()
'
' CYCLE Macro
'
' Keyboard Shortcut: Ctrl+p
'
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
Application.PrintCommunication = True
Range("K1").Select
ActiveCell.FormulaR1C1 = "TAG"
Range("L1").Select
ActiveCell.FormulaR1C1 = "COUNT"
Range("M1:O1").Select
Selection.Merge
ActiveCell.FormulaR1C1 = "NOTES"
Rows("1:1").Select
Selection.Style = "Heading 2"
Selection.Copy
Columns("A:A").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlRight
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = "________"
'FILL DOWN
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=0,(IF(RC[-4]="""","""",(IF(RC[-3]=2,"""",""pull"")))),"""")"
Range("K2").Select
'FILL DOWN
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Dim I As Long, J As Long
J = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For I = J To 2 Step -1
If Range("C" & I).Value <> Range("C" & I - 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Range("C" & I)
End If
Next I
'NEED TO COPY ROW 1 AFTER EVERY BREAK
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Display More
Thank you for any and all help