VBA code to add a new row at the end of data

OldDude

New Member
Joined
Dec 23, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. MacOS
Hi Guys,

I am a newbie to VBA but experienced with Excel. I have a data entry sheet with multiple columns of data including assorted formulas. I have created a macro button and need the VBA code behind it it to do the following:

  1. Find the last data row in the sheet each time.
  2. Click and drag that row down onto the next row essentially same as a click and drag using my mouse.
  3. So each time I click the button it creates a new record (row) under the last data row ready for data input.
If I create numerous "empty record" rows manually it messes up a pivot table I have as they appear as "Blanks" in a slicer window which I don't want, and yes I have tried all the suggestions about removing blanks from the slicer but none worked plus found it is directly linked to the data table.

Hence my approach to simply add new records as required.

Thanks

Old Dude
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use this. Create a button and assign this macro to it.
It transfer all of data inputed at row 1 to after lastrow
VBA Code:
Sub AddRecord()
Dim Lr As Long
Dim i As Long
Dim j As Long

Lr = Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
 Cells(Lr + 1, j).Value = Cells(1, j).Value
 Next j
 Cells(1, 1).EntireRow.ClearContents
End Sub
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
631
Office Version
  1. 365
Platform
  1. Windows
Hi.
VBA Code:
Sub DuplicateLastRow()
 Cells(Rows.Count, 1).End(3).Resize(, 8).Copy Cells(Rows.Count, 1).End(3)(2)
End Sub

Resize(, 8) ~~~> this copies up to column H, adjust as needed
 

OldDude

New Member
Joined
Dec 23, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. MacOS
Sub DuplicateLastRow() Cells(Rows.Count, 1).End(3).Resize(, 8).Copy Cells(Rows.Count, 1).End(3)(2) End Sub
Thanks Osvaldo, this is close but not quite right. While the code reproduces the last row, I don't want the data copied over, just any formulas associated in the cells. So when I go to the last row and click and drag that row onto the next row below it copies the row and all formulas but not any of the data from the previous row. (if that makes sense?).

see screenshot.....
 

Attachments

  • Screen Shot 2020-12-24 at 1.29.14 pm.png
    Screen Shot 2020-12-24 at 1.29.14 pm.png
    19.1 KB · Views: 8

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
631
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi. Please, see if we got a little closer.
Based on the screenshot of your sheet it seems that data and formulas take 10 columns, so I adjusted Resize (,10) to reproduce from A:k, only formulas.
note - to get the last row with data the code takes as reference the last cell filled in column A.
VBA Code:
Sub ReproduceFormulas()
 Cells(Rows.Count, 1).End(3).Resize(, 11).Copy Cells(Rows.Count, 1).End(3)(2)
 Cells(Rows.Count, 1).End(3).Resize(, 11).SpecialCells(2) = ""
End Sub
 

OldDude

New Member
Joined
Dec 23, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. MacOS
Sub ReproduceFormulas() Cells(Rows.Count, 1).End(3).Resize(, 11).Copy Cells(Rows.Count, 1).End(3)(2) Cells(Rows.Count, 1).End(3).Resize(, 11).SpecialCells(2) = "" End Sub
Getting closer, The code works fine for the first time but on the second time it produces an error 1004. See attachment. Also can the final line of code locate the cursor to column A of the newly created row?

Thank you.
 

Attachments

  • Screen Shot 2020-12-26 at 3.39.41 pm.png
    Screen Shot 2020-12-26 at 3.39.41 pm.png
    106 KB · Views: 4

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
631
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Sub ReproduceFormulas()
 Cells(Rows.Count, 1).End(3).Resize(, 11).Copy Cells(Rows.Count, 1).End(3)(2)
 On Error Resume Next
 Cells(Rows.Count, 1).End(3).Resize(, 11).SpecialCells(2) = ""
 Cells(Rows.Count, 1).End(3).Activate
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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