Code
Sub CopyNov_21()
Dim WS1 As Worksheet, WS14 As Worksheet
Dim LastRow As Long
Dim rngCopy As Range, rngSpecialPaste As Range
Set WS1 = Worksheets("Invoice")
Set WS14 = Worksheets("Nov_21")
With WS14
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("K9:K" & LastRow).Formula = "=SUM(F9:J9)"
.Range("B9:B" & LastRow).Formula = "=IF([@[Invoice '#]]=Invoice!$E$4,""Drone Service"","")"
.Cells(LastRow + 1, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
End With
With ActiveSheet
Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
Set rngSpecialPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)
End With
End SubThe above VBA was working but not after I added the Red Font line. Now it want work.