Copy from an unknown table and sheet, then locate and replace on a known table and sheet.

[keelinglee]

Here's the situation...

I have a workbook with 4 worksheets.

1. Title page
2. Loggers (contains primary source table - explained below)
3. Onsite Template sheet (produces secondary source tables - explained below)
4. Formula sheet (contains data for pulldown lists that are within all tables)

The 2nd worksheet ("Loggers") has a source table titled "Record" that has a data range of B29:Q78 (column headers are B28:Q28). There's a button that when the user selects (after making a date input), it does an advanced autofilter on "Record", with the results displaying on the 3rd worksheet ("Onsite Template"), then finally, makes a copy of "Onsite Template" (naming it with the date provided) and inserts it as the new 3rd worksheet, pushing "Onsite Template" and "Formula Sheet" back one level. Each time this button is pressed, it copies "Onsite Template" (regardless of it's position) and inserts the new sheet in the #3 position, pushing all others (including "Onsite Template") back one sheet.

"Onsite Template" has 2 tables and 2 buttons, and when the sheet is copied, the buttons are clearly copied with it because the entire tab is copied. When this happens, this new worksheet with it's new tables, are now the source worksheet, and the tables are the source tables, and they will communicate with the original table "Record" which is located on "Loggers".

Now onto the new source tables. Both of the tables are designed from the table "Record" on worksheet "Loggers". The 2nd table has the exact layout (column names) it's just not as many rows.

• Table 1 - Runs from B10:M30 (including header). Every column header exists on "Record", and also in the same order, just doesn't have all the columns (it skips column's 8-9,13, and 16 of "Record").
• Table 2 - Runs from B36:P56 (including header). Has the same column headers, and in the same order, as "Record". Except for the last column. "Record" has one extra column, which is formulated, and is not on Table 2.

My Question: I'm after a macro for each button (Button 1 and Button 2).

Here's what I need to happen (I know... "FINALLY!"). In all tables, the 4th column (Trk #) will be a 3 or 4 digit number, and this will never change. On the new sheet, the user will make changes in Table 1 or Table 2 (any other column other than Trk #). Then when they select a button (Button 1 for changes in Table 1, Button 2 for changes in Table 2), the macro will search "Record" for each of the Trk #'s on "Table ?", and replace the data on "Record" with the data on "Table ?" if the Trk # exists on "Record" or populate an empty row on "Record" if the Trk # isn't already there. The problem lies in that I will not now the name of this worksheet each time, nor the table names, so the the buttons will have to refer to them as the table on the page, or mark it as an "active table" and then name it (like - set Tbl = active table... or something).

For Table 2 the entire table row will replace the row on "Record" except for that last column (remember that "Record has one extra formulated column at the end that needs to not be touched). It has an IF statement that results in "Yes" or "No" which makes the criteria for the filter. So Table 2's entire row (col's 1-15) will go to Record's 1-15 (leaving 16 alone).

For Table 1's entire row (col's 1-12), it'll be slightly more difficult - Table columns 1-7 are the same, but Table 1's column 8 will go to "Record" column 10, 9 to 11,10 to 12, 11 to 14, 12 to 15 (and once again, leaving "Record" col 16 alone).


Clear as mud?

I've attached a copy of what I'm explaining, but please note that Table 2 doesn't fill in like Table 1 does, because I haven't yet put in the populating instructions (I'll do that later). It will receive information though in the same way.

Thanks in advance for any help in this matter.