VBA - How to make two different cells equal to each other no matter which is changed

[ShellGames]

So I actually have a few questions, I'm attempting to make an excel sheet to increase productivity within my group.

1. How do I make two different cells equal no matter which is changed?

I found this code below to work, but I want to make it work for every value in a column EG - C1 is linked to D1 and C2 is linked to D2, etc. Sometimes there will be empty cells in column C or D, so I need it to look for the last cell in a column that has a value. I would also like it to be passive so that C1 automatically updates when D1 is changed, or vice versa.

"Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$E$1" Then
Application.EnableEvents = False
ActiveSheet.Cells.Cells(2, 5) = ActiveSheet.Cells(1, 5)
Application.EnableEvents = True
Else
If target.Address = "$E$2" Then
Application.EnableEvents = False
ActiveSheet.Cells(1, 5) = ActiveSheet.Cells(2, 5)
Application.EnableEvents = True
End If
End If
End Sub"

I found this code below to almost do exactly what I want - but I have to run an actual macro, whereas I would like it to be passive, like the macro above. I had to modify it a little, which is why there are quite a few '. This one also does not find the last cell with a value, it goes up until the first blank cell.

I tried combining the macros above and below, but the nuances of VBA have prevented me from doing so successfully. I think certain code in the macro below can't be run passively - I just do not know why. Sorry if passive isn't the right word

"Sub Worksheet4to5()
Application.EnableEvents = False
Dim CurrRow As Integer
Dim CurrCol As Integer
Dim TargetRow As Integer
Dim TargetCol As Integer

Dim Sheet4 As String
Dim Sheet5 As String

Sheet4 = "Sheet4" ' name to your source sheet name
Sheet5 = "Sheet5" ' change to your target sheet name
CurrRow = 1
CurrCol = 1 ' This is column A

TargetRow = 1
TargetCol = 2 ' This is column B
TargetRow = TargetRow + 1 'how many spaces down


' Cycle through all rows in Col A until empty field reached
While Sheets(Sheet4).Cells(CurrRow, CurrCol) <> ""

' See if there's a value in column B in the same row - if so, go to the next row
'While Sheets(Sheet5).Cells(TargetRow, TargetCol) <> ""
'TargetRow = TargetRow + 1 'how many spaces down
' Wend

' Copy the value from the source to the target
Sheets(Sheet5).Cells(TargetRow, TargetCol) = Sheets(Sheet4).Cells(CurrRow, CurrCol)

' Move to the next source and target rows
CurrRow = CurrRow + 1
TargetRow = TargetRow + 1

Wend
Application.EnableEvents = True
End Sub"

2. I have an excel sheet that I want to be able to create code to generate the exact same sheet with the same spacing, formatting, etc. I can't copy and paste for a few reasons. Anyway, we download data from a database, and I'd like to run a macro that creates a duplicate of an excel sheet that I currently have on my desktop. Like with HTML, you can view the code of a website by right-clicking anywhere and clicking 'view page source' (at least for chrome), and if you copied and pasted that code, it would create a duplicate of that website. I'm looking to do the same thing with a certain excel sheet. --- obtain the code so I can just copy and paste the code into a macro to generate the same sheet.

If this isn't possible, I'll have to do it the very long and tedious way of manually coding every single thing in.

 

[ShellGames]

Shameless bump? Does anyone know if the second thing is even possible?

 

[ShellGames]

Bump #2