Macro to copy formula down.

Z

Zillay

New Member
Joined
Mar 28, 2021
Messages
23
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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
 
Upvote 0
Fluff said:
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
Click to expand...
Thanks Fluff: i ran the code and got the following error

Runtime error: 1004
Paste special method of range calls failed
 
Upvote 0
Oops, it should be
VBA Code: 
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
 
Upvote 0
Fluff said:
Oops, it should be
VBA Code: 
   Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(Usdrws).Copy
Click to expand...
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
 
Upvote 0
Zillay said:
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
Click to expand...
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:
Upvote 0
Do you have any values in col A of the original sheet?
 
Upvote 0
If you step through the code using F8, does the correct amount of data get copied to the advanced-payroll-activity_14032 sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
354
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
478
Coyotex3
C
D
  • Solved
AutoFill or Copy Formula to All Relevant Cells
Replies
5
Views
789
deZine
D
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
462
Micron
M

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top