Adding new rows but keeping totals etc. suitably adjusted

[H28Sailor]

Hi

In an earlier post, I was given a subroutine from Mike7952 which copies rows of data from one worksheet to other worksheets
This will be done on a weekly basis with new data captured each week

The code was as follows

Please Login or Register  to view this content.

Is it possible to have some totals, averages etc calculated say 2 rows below the last row of data and be able to then copy a new set of rows of data below the last existing row of data and before the totals, averages etc row?

Bob M
Dunedin
New Zealand

[AlphaFrog]

Try this. Have your formulas include the blank row in between so when a new row is inserted, the formulas will automatically update and include the new data.

Please Login or Register  to view this content.

[H28Sailor]

Thank you...................

Run-time error = '9'
Subscript out of range

7th row highlighted - Set ws =.............

Bob M

[AlphaFrog]

Change that line to this...

Set ws = Worksheets(UCase(Trim(.Cells(iLooper, "A").Value)))

[H28Sailor]

Thank you...................

Run-time error = '9'
Subscript out of range

7th row highlighted - Set ws =.............

Bob M

[H28Sailor]

Thanks..................

Run-time error '1004':

Application-defined or object-defined error

Highlighted 9th row ws.Rows(NextRow).Insert

[AlphaFrog]

Please Login or Register  to view this content.

[H28Sailor]

Thank you.............no run-time errors but some interesting results

(1) Enrollment worksheet
following the existing data - we have a blank row
then all the new rows added
then immediately followed by the totals row - BUT totals etc NOT adjusted
Do my totals formulae require an offset or relative address?

(2) Weekly worksheets
I have no totals etc at the moment which is OK, but all new rows are copied immediately next to the existing rows plus the last two are copied again

Bob M

[AlphaFrog]

Perhaps you could attach an example workbook.

[H28Sailor]

Hi

workbook attached

Bob M

[AlphaFrog]

Try this. The ListObject tables were throwing off the Last data row calculation. This new code uses column B to determine the last data row.

[H28Sailor]

Hi Alphafrog

Many thanks for your replies and time
Excellent result

Bob M

[AlphaFrog]

You're welcome.