Marcus

I didn't realise the code should not have been placed in ThisWorkbook sheet
€“ Im still learning :)

How do you know which row you want the formula copied from, you did
not say in your post.

There are around 8000 unique records in colB sorted alphabetically. When
additions are added these need to be entered in the appropriate location to
maintain the integrity of the alphabetical list.

How about if you use the last USED Row in Column A as the row
you want to insert a line and pull the formula from the above cell.

Every other column starting from €œA€� to €œBE€� contains a relative formula.
There are eight other sheets that contain identical data and formatting.
Using the last row will not be suitable as I would need to then sort all the
sheets With the amount of data and formulas in each sheet this would take a
very long time for a sheet calculation to be carried out.

So if a new record needs to be added then the location in the alphabetical
list is identified and the appropriate row is highlighted then when the code
is run it will know where to place an empty row.

What way should the code now be constructed to achieve this requirement?


"marcus" wrote:

Gotroots

I made the bold assumption you would place this code in a Normal
Module (the same place your recorded macro is held). Of course if you
place the code in ThisWorkbook it will fail, placing it in any normal
module or even one of the sheet modules would see it go like the
clappers.

How do you know which row you want the formula copied from, you did
not say in your post. I just used the selected row as a starting
point. How about if you use the last USED Row in Column A as the row
you want to insert a line and pull the formula from the above cell.
This will accomplish this also with the addition of Columns C and E.
Please put this in a normal module.


Take care

Marcus

Option Explicit
Sub AddRowtoAll()
Dim ws As Worksheet
Dim AR As Integer 'active row

AR = Range("A" & Rows.Count).End(xlUp).Row 'Last used row in Col A,
Change to suit.

For Each ws In ThisWorkbook.Worksheets
If Left((ws.Name), 5) = "Usual" Then
ws.Range("A" & AR + 1).EntireRow.Insert
ws.Range("A" & AR).Copy ws.Range("A" & AR + 1)
ws.Range("C" & AR).Copy ws.Range("C" & AR + 1)
ws.Range("E" & AR).Copy ws.Range("E" & AR + 1)
End If

Next ws

End Sub
.