Solved How to write Macro to add additional rows with existing form

February 2, 2015 at 13:37:09
Specs: Windows 8.1
I have a multi-tab workbook and am trying to solve 2 issues with 1 macro.

The workbook contains a main tab with all of the 'entered info' that I am collecting. Based upon results in the main tab, I have 3 additional tabs that update. Essentially, the main tab is 'data entry' tab and the other 3 are 'report' tabs.

At some point, my main tab needs to have new rows inserted to handle additional data. I would like to insert new rows with all of the existing formulas/formatting etc with a macro (I know how to do it manually) AND, I would like to have the same thing happen in each of the additional 3 tabs. 1 macro that inserts a new line with formula/formatting into 4 tabs in the workbook at once.

Is is possible? Anyone have suggestions? The main reasons that I'm trying this is for
- simplicity
- error prevention when someone takes over the effort


See More: How to write Macro to add additional rows with existing form

Report •

February 3, 2015 at 07:03:44
It would be fairly simple to write a macro that loops through all sheets in a workbook and inserts rows, or even selects them all and does it in one step, but without more detail as to where the rows are supposed to go - in each sheet - there's not much we can offer.

Please provide some specifics about your task.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 3, 2015 at 08:43:32
Thanks for your reply DerbyDad.

My needs are pretty basic on this spreadsheet. I'm looking to add an additional row at the 'bottom of the stack' on all of the tabs with formulas copied from the line above.

In the subject spreadsheet, all of the tabs have data up to row 78. Rows 79 & 80 are blank. Row 81 contains formulas that sum up items in various columns.

Row 78 in each tab is unique.

What I'm looking to do in each tab is to copy row 78, insert a new line below it, and paste in the formulas. That new line is inserted above my 'total' line (81), so those formulas should stay intact.

For the sake of ease, we'll call the tabs TAB1, TAB2, TAB3 and TAB 4

For each new line after, it can copy the line above (it does not need to be line 78).

Please let me know if any additional info is needed.

Report •

February 3, 2015 at 15:58:10
✔ Best Answer
Try this...

As written, the code uses Column A to determine the last Row with data (your
"Total" row). Modify as required.

Sub NewLastLine()
Dim ws As Worksheet
'Loop through worksheets
  For Each ws In ThisWorkbook.Worksheets
'Determine last row with data in ws
    lastRw = ws.Range("A" & Rows.Count).End(xlUp).Row
'Row to be copied is 3 Rows up
     srcRw = lastRw - 3
'Copy, insert Row
      ws.Rows(srcRw + 1).Insert shift:=xlDown
  Next ws
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 4, 2015 at 06:54:42
Thanks DerbyDad03. I'll give it a shot shortly. I had some unexpected business travel pop up. I'll let you know how the code works out.

Much appreciated.

edited by moderator: Edit

Report •

Ask Question