Getting Macro to dynamically adjust to a new row

Hi there,

I've tried to write a macro that creates a tab for a new room and adds it to the totals. This works the first time I do it, however from the 2nd time onwards it creates a new tab but the totals are still based on data from the first tab I created "Room 2 (2)". I would need this to now be for "Room 2 (3)" instead. How can I make it so the macro accounts for this?

I really don't know much about VBA, I did the macro through the recorder.

Thanks

Does the attached work for you? It takes values from certain cells in the Room worksheets and adds linking formulas to the Totals sheet. You'd need to already have all the Room worksheets in the workbook and ensure that they are positioned between the Temp_tables and the Totals worksheets.

I didn't receive a PM but you can ask a question here if it relates to the spreadsheet under consideration.

Thank you again for your help. I was actually hoping to take the sample you provided and apply it to the full spreadsheet but unfortunately what you've done is beyond me. If it's not too much trouble, are you able to apply what you did to this spreadsheet? Would it be able to update both the "1 Totals" tab and "2 Totals" tab. I've once again made a basic macro "Macro1" that adds one new line to both the "1 Totals" and "2 Totals". I can understand if this request takes up too much of your time and thank you for the help prior.

A couple of clarifying questions:

Are you looking for essentially the same formulas to be copied to both 1 Totals and 2 Totals (on your example, it's the content in cells A6 to H25)?
The various text and calculations shown on each Totals sheet starting in row 27- are these already in the Totals sheets before the copied data is to be entered?

Let me know and I can take a look.

So- there would never be a need for the macro to insert anything beyond row 26? Or is that a possibility? And there would only be Totals 1 and Totals 2 worksheets- nothing more?

Does the attached work? I haven't included a clickable shape on this one as there are multiple sheets, so just run the macro from the Developer tab (or wherever you have set up to run macros).

OK- I wasn't aware that columns F, G and H needed coding too. That is now done.

You might need to check the contents of the individual cells in the GF1 and FF1 etc sheets as they are currently zero in the version I was using- hence the zero values on most of the columns C to E cells.
Also, the DIV0 errors are s a result of this- dividing by zero gives an error in Excel.

I'm not sure why the last few rows seem to repeat the say text- it wasn't happening on my test run.

One major issue to clarify: I'm still not quite clear how this is wanted to work- my assumption is that you have a bunch of sheets like the GF1 to GF10 and the FF1 to FF10. You also have 2 Totals worksheets and essentially that's it.

Is the code supposed to work if you were to add say another 10 sheets (maybe for second floors of a building so SF1 to SF10) and then you'd also add a Totals 3 worksheet? It isn't doing that at present as it wasn't my understanding that this was required.

If my understanding is wrong, please add a full description of the potential scope of the code.

Hi I ran the macro a few times but I assume I must have messed something up that I sent over as the rows were misaligned. The macro worked perfectly from A to E but was reading from the wrong cell from F onwards. I've done a new one with the macro (which works as intended for the first room but not for the rest).

What I'm looking to do is add a new room which is then shown on both "1 Totals" and "2 Totals". So by default the sheet has 20 rooms (which is usually enough), however if I need to account for 50 rooms on a project then it takes ages to manually add the other 30 rooms.

The macro adds a new tab (room) then adds the information required for the "Totals 1" the copy pastes it into "Totals 2". The previous one sent over had some errors when the macro was run such as reading from C6 for every row for the F column instead of from the C column in the same row. Each row needs information from A-U and then W. How the macro runs for the the first room added is how I'd need it to be for the following rooms. Nothing below the row added needs to have anything to do with the macro. Thanks

I see the hard-coded error I had in columns F to H- apologies for this. I'm pretty certain that the attached now correctly adds correct formulae to those columns.

Could you test the code by adding an extra couple of GF or FF type sheets (they can be for other floors so any name is fine e.g. SF or whatever, just keep them to the left of the Totals sheets) - just confirm if it is doing what is required.

Hi, Am I doing something wrong? I've opened the sheet with security disabled and it looks like the top picture with all the info missing. I then tried adding a new room "FF10 (2)" and I get the error message when I try to run "copydatatototalssheet" macro as seen in the second picture.

GEM1.png

You need to run the macro called CopyDataToTotalsSheet to get the formulae into the various cells.

I had cleared them out to test the macro and once this macro is run, you should see all the formulae back in place.

Use the attached workbook- it's updated to allow for an address error in the previous one.

The attachment isn't valid- maybe post two workbooks showing before and after if possible.

I don't yet fully grasp the problem - until I understand what is/ isn't happening I'm running blind!

EDIT- the reference to "two extra lines for "I" onwards at the bottom which relate to FF9 and FF10." is probably because you copied FF9 and FF10 when adding new sheets but didn't change the content of cells in those newly copied sheets, so whatever is in the cell B1 etc of the new sheets will match what's in the original sheets that were copied and is displayed in the newly added rows.

OK- so looking at the imgur image:

Pic 1 and 2 is the 'before running the macro'.
Pic 3 and 4 is the result of running the macro. It looks like you copied sheets FF8,FF9 and FF10 (twice for FF10) before running the macro to see how extra rooms are handled. I can see the blank cells in columns I and later. Are these cells also to be included in the macro? Column I value is not a formula so the default could be set to RAD; others are formulae, across to column W.

Am I to assume that the Totals sheets could be showing any number of rooms before the macro is run. On running the macro, it needs to first of all clear out everything displayed, then adjust the number of displayed rows up or down (where the default is to show 20 rooms minimum but a variable maximum) and finally enter the various formulae into cells from A to W of each displayed row?

>I can see the blank cells in columns I and later. Are these cells also to be included in the macro? Column I value is not a formula so the default could be set to RAD; others are formulae, across to column W.

Yes please, "I" only displays text that reads "RAD" or "UFH" (RAD is fine for default). "K" to "U" uses formulas that need to be replicated for every new room. Then "W" also uses a formula that needs to be replicated for every room.

Ideally the totals sheet should be showing the original 20 rooms "GF1-GF10" and "FF1-FF10" with all the text visible. Then, after creating a new room (tab) and running the macro, the relevant information is shown in a new line at the bottom of the original rooms showing "Totals 1" and "Totals 2".

Picture attached shows how I'd like totals page to look to begin with.

Then picture 2 shows how it would look after I've added one new room and run a macro. Issue I had was duplicating this beyond the first room because the totals would always reference back to the first new room I made.

GEM3.png

OK. I think I now have a proper understanding of what is required.

I am currently on another task so will look at this later.

The approach I'll be taking with the macros is as follows:

1. Initially clear out everything displayed for the various rooms in columns A to W;
2. Run the code which will initially check if there needs to be another row or rows added (or deleted) for extra rooms or deleted rooms;
3. Programmatically update all the formulae in the columns A to W;
4. Add RAD as the default for column I (NB- you'd need to manually modify anything that should read UFH, so if that's a problem I'd need to think of how to fix it).

I think that should tick all the boxes.

Yes, whenever's fine, you've gone above and beyond.

By default the 20 rooms would be on Totals 1 and Totals 2. If I need an extra room I'd copy the end room ("FF10" in this case) which would then generate "FF10 (2)". Then when I run the macro it would populate new lines at the bottom of totals 1 and totals 2 for FF10 (2) with the same relevant information as was in the original 20 entries. Obviously I'd like to be able to replicate this. So if I needed a 22nd room I'd copy "FF10 (2) to a new tab then run the macro which would add it to the totals. RAD is fine as default for "I".

Give the attached workbook a run to see if it's doing what's required. The cells are currently blank on the Totals sheet- they'll be filled in once the macro is run. You can add an extra couple of sheets and rerun the macros to see if it's OK.

Leave a message here either way to let me know if all's OK or if there's something further needed.

You're welcome and thanks too for the reputation boost!

Try changing this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Sheet23 is the VBA codename for Totals 1