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

ShellGames

New Member
Joined
Jul 27, 2017
Messages
3
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,687
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top