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

[OldDude]

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

 

[maabadi]

Use this. Create a button and assign this macro to it.
It transfer all of data inputed at row 1 to after lastrow

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]

Hi.

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]

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.....

 

[Osvaldo Palmeiro]

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.

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]

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.

 

[Osvaldo Palmeiro]

Try this:

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

 

[OldDude]

Thank you Osvaldo, that worked well.