Macro to copy formula down.

[Zillay]

Hi, I am very new to Macro.
I recorded a macro in the excel book to do the following
1 - clean up data on Raw Data Sheet
2 - Copy data from Raw Data Sheet to advanced-payroll-activity_14032 starting from cell C2
3 - copy formula in A2 and B2 Cells down to the last record of C column
4- Copy data from Column A and B in advanced-payroll-activity_14032 to Data Consolidation Sheet and keep unique value of Jobs and Accounts
5 - copy formula down to the last record in F and G in Data Consolidation Sheet.

the macro recorded Cells Value e.g A2:A368.
i need to have a code to select the last record instead. Please help to modify the codes below. i have highlighted the step 3 code in Green

Below is the code

Sub DC()
'
' DC Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("O:P").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("advanced-payroll-activity_14032").Select
    Range("C2").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
   Selection.AutoFill Destination:=Range("A2:A386")
    Range("A2:A386").Select
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B386")
    Range("B2:B386").Select
    Range("A2:B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Data Consolidation").Select
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("H2:H30")
    Range("H2:H30").Select
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I30")
    Range("I2:I30").Select
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J30")
    Range("J2:J30").Select
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K30")
    Range("K2:K30").Select
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L386")
    Range("L2:L386").Select
    ActiveWindow.ScrollColumn = 4
    Range("F2:G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$F$1:$G$386").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H30")
    Range("H2:H30").Select
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I30")
    Range("I2:I30").Select
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J30")
    Range("J2:J30").Select
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K30")
    Range("K2:K30").Select
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L386")
    Range("L2:L386").Select
    Range("M30").Select
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N30")
    Range("N2:N30").Select
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
End Sub

 

[Zillay]

yes.

Ok, the following code worked finally

Sub DC1()
Dim Usdrws As Long
Dim Finalcount As Long

Usdrws = (Sheets("Raw Data").Range("A" & Sheets("Raw Data").Rows.Count).End(xlUp).Row - 1)

Sheets("Raw Data").Range("E:E,L:M,R:S").Delete

Sheets("Raw Data").Range("A2", Sheets("Raw Data").Cells(2, Sheets("advanced-payroll-activity_14032").Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Sheets("advanced-payroll-activity_14032").Range("C2").PasteSpecial


Sheets("advanced-payroll-activity_14032").Range("A2:B" & (Usdrws + 1)).FillDown

Sheets("advanced-payroll-activity_14032").Range("A2:B" & (Usdrws + 1)).Copy

Sheets("Data Consolidation").Range("F2").PasteSpecial xlPasteValues


Sheets("Data Consolidation").Range("F1:G" & (Usdrws + 1)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Finalcount = Sheets("Data Consolidation").Range("F:F").Cells.SpecialCells(xlCellTypeConstants).Count

Sheets("Data Consolidation").Range("H2:N" & Finalcount).FillDown

End Sub

 

[Fluff]

Glad it's sorted & thanks for the feedback.