Clone Excel Sheet Structure but Change Cell Values

[RFraser86]

Hello all

So there's a TL;DR (Too long - didn't read) here which is a summary...
_______________________________________
TL;DR

I want to find a way, via macro / vba to do this........
- "Search for cell values that aren't a formula in Worksheet 1"
- "Select those values"
- "Find a column in Worksheet 2 with the same column heading (Row 1) and row reference (column A) as the cells selected in Worksheet 1"
NOTE - these may have moved
- "Paste Values and number formats in the right places"
- "Keep everything else as a formula and leave it alone"

..... or this:
- Have Worksheet 1 and Worksheet 2 dynamically linked - changes to structure (i.e. add columns, remove columns, add rows, re-arrange columns) and certain cells (i.e. Column A:A, and Rows 1:5) in "Worksheet 1" are automatically picked up on "Worksheet 2"... But all values within a certain range (i.e. B6:AZ2000) in Worksheet 2 are calculated by a formula from the values of Worksheet 1... but the user can manually override any values in "Worksheet 2" that they please.
______________________________________________________________

I've got a very interesting, and hopefully, solveable problem.

I have developed a water balance spreadsheet. In this spreadsheet I have 2 tabs - "Flow Meter Entry" and "Flow Meter Monthly".
- "Flow Meter Entry" holds information on flow meters that measure how much water has travelled around a mine - i.e. one month the meter will read 10,000. The next month the meter will read 11,000.
- "Flow Meter Monthly" does the sum to calculate how much water has moved through the flow meter in that month - i.e. 11,000 - 10,000 = 1,000. It's done with a very large nested IF statement to catch a whole heap of errors that may occur etc.


The data are arranged in columns - i.e. each date is a new row, and each flow meter is a new column. There are about 30 flow meters in total, and the columns are grouped (put beside each other) by type (i.e. some that measure potable water, some that measure mine water, some that measure greywater etc.). There are a whole heap of Conditional Formatting Rules in the "Flow Meter Monthly" tab as well.

I've developed this spreadsheet so that people who are not great at excel, can just plug in the "Total numbers" that they read off the flow meters, and it will automatically calculate how much water has gone through the flow meter between dates.

Now the problems:
a) the mine site will want to add new flow meters as they install them (i.e. add a new column in the midst of existing columns)
b) sometimes on the "Flow Meter Monthly" tab, the site will have to manually enter values instead of relying on the formula (for a variety of reasons).

I want the 2nd excel sheet, "Flow Meter Monthly" to pick up on changes done to the structure of "Flow Meter Entry" so that if a new column is added in the middle of data, it is automatically done in "Flow Meter Monthly".

The only way I've come up with to do this is to make a macro and edit the VBA Code so that, that, when it is activated (when the user hits a button after entering data on "Flow Meter Entry"), the following happens:
- The entire worksheet of Flow Meter Entry is copied to a new workbook (Flow Meter Entry (2))
- The nested IF statement calculation is applied to all relevant cells
- All formula results in the "Flow Meter Entry (2)" are copied and pasted as values (just because)

I then want to make it, so in said macro, the sheet "Flow Meter Monthly" is deleted, "Flow Meter Entry (2)" is renamed to "Flow Meter Monthly" and then a whole heap of conditional formatting is applied.... but the problem is that I want any manual changes to the values in the original "Flow Meter Monthly" (i.e. manually overridden cells) to be picked up and put in the relevant column (which may have moved) of the "Flow Meter Entry (2)". So essentially is there a way in VBA to go
- "Search for cell values that aren't a formula in Worksheet 1"
- "Select those values"
- "Find a column in Worksheet 2 with the same column heading as the cells selected in Worksheet 1"
- "In Worksheet 2, Find the row reference (date) with the same row reference (date) as Worksheet 1"
NOTE - these may have moved
- "Paste Values and number formats"
- "Keep everything else as a formula and leave it alone"

If I can do that, then I'll get the macro to keep on going and delete "Flow Meter Monthly" and re-name "Flow Meter Entry (2)" to "Flow Meter Monthly" and it's all sorted - I just have to be sure that I can pick up the manually over-ridden values put in "Flow Meter Monthly" by other people at the mine site, and incorporate them into "Flow Meter Entry (2)" before deleting the old worksheet.

...............
Alternatively if there's a way to have "Flow Meter Monthly" as a clone of "Flow Meter Total" so that if a column is added or moved in "Flow Meter Total" it is also added or moved in "Flow Meter Monthly" etc... but the values in certain cells are calculated based on a formula in "Flow Meter Monthly" from the values in the cells of "Flow Meter Total" (and can be manually overriden).

_____________________________________

Any help on this would be GREATLY appreciated. I've only got very basic skills in VBA (I record a macro in excel, then go into VBA, try to understand what's going on (most of the time its not hard) and change things to make it work for me (i.e. worksheet names based on a cell value, save worksheet as a PDF with a file name based on cell values, etc.)

Cheers

Reece

[Richard Buttrey]

Hi,

If you'd like to upload the workbook and then describe how you might make a change to sheet1 and then manually add a third sheet which shows the effect of the changes so that we can see how the format differs to sheet2 we may be able to offer a bit more help.