Macro to copy formula down.

Zillay

New Member
Joined
Mar 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Rich (BB 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
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel, how about
VBA Code:
Sub DC()
   Dim Usdrws As Long
   
   Usdrws = Range("A" & Rows.Count).End(xlUp).Row
   
   Range("E:E,L:M,R:S").Delete
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Select
   Sheets("advanced-payroll-activity_14032").Select
   Range("C2").PasteSpecial
   Range("A2:B" & Usdrws).FillDown
   Range("A2:B" & Usdrws).Copy
   Sheets("Data Consolidation").Select
   Range("F2").PasteSpecial xlPasteValues
   Range("F1:G" & Usdrws).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
   Range("H2:N" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
   Range("H2").Select
End Sub
 

Zillay

New Member
Joined
Mar 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel, how about
VBA Code:
Sub DC()
   Dim Usdrws As Long
  
   Usdrws = Range("A" & Rows.Count).End(xlUp).Row
  
   Range("E:E,L:M,R:S").Delete
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Select
   Sheets("advanced-payroll-activity_14032").Select
   Range("C2").PasteSpecial
   Range("A2:B" & Usdrws).FillDown
   Range("A2:B" & Usdrws).Copy
   Sheets("Data Consolidation").Select
   Range("F2").PasteSpecial xlPasteValues
   Range("F1:G" & Usdrws).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
  
   Range("H2:N" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
   Range("H2").Select
End Sub
Thanks Fluff: i ran the code and got the following error

Runtime error: 1004
Paste special method of range calls failed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows
Oops, it should be
VBA Code:
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
 

Zillay

New Member
Joined
Mar 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oops, it should be
VBA Code:
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Hi Fluff,
i have the following modified code and got error on Range("C2").PasteSpecial

Sub DC()
Dim Usdrws As Long

Usdrws = Range("A" & Rows.Count).End(xlUp).Row

Range("E:E,L:M,R:S").Delete
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Select
Sheets("advanced-payroll-activity_14032").Select
Range("C2").PasteSpecial
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Sheets("Data Consolidation").Select
Range("F2").PasteSpecial xlPasteValues
Range("F1:G" & Usdrws).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Range("H2:N" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Range("H2").Select
End Sub
 

Zillay

New Member
Joined
Mar 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,
i have the following modified code and got error on Range("C2").PasteSpecial

Sub DC()
Dim Usdrws As Long

Usdrws = Range("A" & Rows.Count).End(xlUp).Row

Range("E:E,L:M,R:S").Delete
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Select
Sheets("advanced-payroll-activity_14032").Select
Range("C2").PasteSpecial
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Sheets("Data Consolidation").Select
Range("F2").PasteSpecial xlPasteValues
Range("F1:G" & Usdrws).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Range("H2:N" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Range("H2").Select
End Sub
Sorry, i have copied wrong codes above,

this is what i have now and Remove Duplicates deleted all values
VBA Code:
Sub DC()
Dim Usdrws As Long

Usdrws = Range("A" & Rows.Count).End(xlUp).Row

Range("E:E,L:M,R:S").Delete
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Sheets("advanced-payroll-activity_14032").Select
Range("C2").PasteSpecial
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Sheets("Data Consolidation").Select
Range("F2").PasteSpecial xlPasteValues
Range("F1:G" & Usdrws).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Range("H2:N" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Range("H2").Select
End Sub

the codes above deleted all data in Coldum F and G in Data consolidation Sheet. i had values only in One cell F2 and G2
the cells in H3:N got incorrect formula.
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you have any values in col A of the original sheet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows
If you step through the code using F8, does the correct amount of data get copied to the advanced-payroll-activity_14032 sheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,106
Messages
5,640,132
Members
417,126
Latest member
Jeffman52

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