Reference an address of a cell in another table on another Sheet

[ac0ua]

I have 2 sheets and one table for each.
The first sheet contains my table1 "Telephony map"
The Second sheet is my table2 "change history" (log changes to table1 "Telephony map")


I do not know the terms for asking the question so here is the situation.
Table2 is a copy of table1 but will be used for logging changes

1. I make a change to table1
2. gather the individual data for each cell in the selected row of table1
3. insert the data to table2

(Table1)
[Name] [phone] [office]
[bob] [345] [002]
[joe] [654] [004]
[sam] [123] [007]

lets make a change
bob and sam switch offices

using VBA I have three variables that hold each cell in the row from table1

***I want to insert this information into each cell in a new row in table2.

(Table2)
[date time] [Name] [phone] [office]
[stamp] [bob] [345] [007]
[stamp] [sam] [123] [002]

information has been logged. This information is helpful for looking up old offices and phone extensions, I have inherited a mess of spaghetti wires and am trying to keep it all organized.

***How do I do this? Am I making this too complicated?

If there are any questions please ask.

 

[Stacko]

Personally, I'd do it the other way around. I would add the changes to Table 2. Then I would make Table 1 a view on Table 2 so that it shows one line per person and the latest details from Table 2.

This way you don't need VBA.

 

[ac0ua]

I want to "log-history" because I have been saving every change I do as a new document. This way if the telephony map has an error I can fix it by looking at the previous documents. (I can look back and see what I have done.) In a 4 month period I an now up to 20 files with the first 19 in an archive folder.

So the "log-history" is just that a log of records that I have changed. This way I can keep track of the changes all in one document.

@Stacko If I have miss understood you please clarify, how I would use the log sheet as a way to change my telephony map. thank you.

If there are any suggestions I am open to them.

 

[Stacko]

What I am saying is you see it as maintaining a view (the latest numbers)and then getting the view to update a log. It seems more sensible to me to maintain the underlying data and then extract from that what you want to see. So you want to hold all the transactions but also only see the current view. So don't make changes to the view ('map'). Add a new row to the log instead. Then derive the map from it. This would be straightforward with SQL. I'm not sure how you do it in Excel offhand. I would guess a pivot would be a good thing to try.

 

[ac0ua]

There is nothing wrong with your suggestion but I have put allot of hours in this Excel document already. If I was going to make a web interface MySQL would be the way to go.
But the excel document is built and I have the information stored in a variable already. All I need is the VBA code to reference the other sheet and range.

While I was replying I figured it out. :D

The insertion point for the log entry will always be the same.
Sheets("log-history").Range("$B$3").Value = strCell_1_value