PDA

View Full Version : Find last row, insert row retaining format of last line and repeat for named tabs



RKramkowski
05-15-2009, 09:01 AM
Hi, I've searched this forum for the component parts to do this to no avail.

I have a multi-tab spreadsheet. The first tab is my control tab. It contains information which then drives events on other sheets.

So I need to find the last row with data on tab 1 and append a new row and copy the formats and formulas from the last row with data to the newly inserted row.

I then need to automatically insert a line at the same position on several other tabs (defined by tabname if possible). Tabs 2 through 12 may have more lines of data than tab 1 but not all tabs will have the line inserted.

All this should be assigned to a button in the first tab.

To describe the effect:

1. User clicks on "Add New Line" button.
2. Macro finds last row with data on that tab (let's say it's line 5).
3. Macro inserts a new line at 6 (after 5), copying the formats and formulas from line 5 to line 6.
4. Macro also inserts new line on tab 2, 4, 8, and 9 after line 5 on each, copying formats and formulas from the preceding line on each respective tab. So the new line 6 on tab 2 brings down the formats and formulas from line 5 on tab 2. New line 6 on tab 4 brings down the formats and formulas from line 5 on tab 4, etc.
5. When done, the macro should leave the person at the first cell in the new line on tab 1.

The macro cannot find the last line on tabs 2, 4, 8, and 9 in order to insert the new line since those tabs could have much more data than tab 1. But I need those lines to be in the same position on those tabs as the new line is in tab 1.

Did I explain that well enough?

MaximS
05-21-2009, 07:45 AM
try somthing like that:


Sub Process()
Dim LRow As Long
Dim i As Integer
With ThisWorkbook
LRow = .Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
.Worksheets(1).Rows(LRow).Copy
For i = 1 To Worksheets.Count
If i = 1 Or i = 2 Or i = 4 Or i = 8 Or i = 9 Then
.Worksheets(i).Rows(LRow + 1).PasteSpecial
End If
Next
End With
End Sub