Macro to add or delete rows in a worksheet

[johncullen01]

I have just started my foray into VBA. I have been trying to figure out an elegant way to add or delete rows in a worksheet based on the contents of that row, but have a runaway macro at the moment.

I have attached the example for reference.

Book1 links to the rows in Book2 (the number of rows in Book2 varies from day to day, but always finishes the same - a blank line and then another line with Report Total =). Manually I go through each of the Sheets and either delete rows or add rows so that the last row of each sheet in Book1 reflects the last sheet of Book2 (The line with Report Total in Column B). I now have 41 sheets to review and it is getting time consuming.

I have written a macro that goes through each sheet and copies the last row down. This process should continue until the "Report Total =" line is reached. Unfortunately it doesn't and carries on adding lines. I haven't worked out how to delete extra lines either.

The way I see the logic is as follows:

If the last row is not blank (0) copy the row down and keep going until the row with Report Total = in column B appears.
If the last row is blank (0) delete the row and keep going until the last row has Report Total = in column B.
If the last row has Report Total = in column B, then stop and go onto the next sheet.

Any assistance would be appreciated.

Thanks

[arlu1201]

Should the data match between both Book1 and Book2?

I am not able to understand what you are trying to achieve.

[johncullen01]

Yes, I am trying to have Book1 with all the lines that are in Book2. I also have Book3, 4, 5, 6, 7 with multiple sheets on them that I essentially want to combine into Book1, but they all follow the same principle.

Thanks
JOHN