Macro to update a cell value to match another cell within the same workbook

[lawi227]

I am looking to create a macro button. I have no experience with macros, etc.

I have a cell (N3) in Sheet 1 (called "16-17M"). I need this value to match a cell (B3) in Sheet 2 (called "Goal Tracking").

I have some conditional formatting that tells me when cell N3 does not match B3. I would like to have a button right next to cell N3 that I can click on and it will automatically update that number.

Note: Cell N3 is used in multiple formulas (such as VLOOKUP) and that is why this cell has to be text and I cannot reference or use a formula.

 

[j_unsuitable]

Hey,

I am not really sure why you cant use ='Goal Tracking'!B3 in '16-17M'!N3

but try

Sub transferB3()
Worksheets("16-17M").Cells(3, 14).Value = Worksheets("Goal Tracking").Cells(3, 2).Value
End Sub

Julian

 

[lawi227]

Hey,

I am not really sure why you cant use ='Goal Tracking'!B3 in '16-17M'!N3

but try

Sub transferB3()
Worksheets("16-17M").Cells(3, 14).Value = Worksheets("Goal Tracking").Cells(3, 2).Value
End Sub

Julian

How would I enter this if I would like to use a button?

 

[j_unsuitable]

Hit Alt+F11 to enter Visual Basic
Then Insert->Module
Paste the code into the newly opened module window
Hit Alt + Q
Insert the Button (Any shape from the "Insert Tab" or an actual Button from the "Developer Tab")
Right click Button -> Assign Macro
Chose the Macro you want to assign

https://support.office.com/en-us/ar...orksheet-d58edd7d-cb04-4964-bead-9c72c843a283

 

[lawi227]

Hit Alt+F11 to enter Visual Basic
Then Insert->Module
Paste the code into the newly opened module window
Hit Alt + Q
Insert the Button (Any shape from the "Insert Tab" or an actual Button from the "Developer Tab")
Right click Button -> Assign Macro
Chose the Macro you want to assign

https://support.office.com/en-us/ar...orksheet-d58edd7d-cb04-4964-bead-9c72c843a283

I tried it and it did not work. I removed the parentheses around the sheet name, but that didn't help.

 

[j_unsuitable]

How did it not work? Did it just not do anything? Did it give an Errormessage?
Did you make sure there actually is a value in the cell B3 in the Sheet "Goal Tracking"?

 

[lawi227]

How did it not work? Did it just not do anything? Did it give an Errormessage?
Did you make sure there actually is a value in the cell B3 in the Sheet "Goal Tracking"?

I did everything you said to and created the button and added the macro. When I click on the button nothing happened. Perhaps I didn't run the macro correctly. And yes there is a number in B3, but it is an equation (=B8+B11).

 

[j_unsuitable]

Mhh..
add this line to the code under sub transferb3()

MsgBox Worksheets("Goal Tracking").Cells(3, 2).Value & " - " & Worksheets("16-17M").Cells(3, 14).Value

and click the Button again, then a Messagebox should pop up, what does it say?

 

[lawi227]

I still don't get a message box or anything. Perhaps I didn't properly load the macro. I think I assigned the correct macro. When I clicked "run" after clicking under the "assign macro" button, this is what I got:

Compile error:


Syntax error
(Module3 2:0)

 

[j_unsuitable]

Did you enter the parentheses into the code again after you removed them?
the code should be:

Sub transferB3()
MsgBox Worksheets("Goal Tracking").Cells(3, 2).Value & "  -  " & Worksheets("16-17M").Cells(3, 14).Value
Worksheets("16-17M").Cells(3, 14).Value = Worksheets("Goal Tracking").Cells(3, 2).Value
End Sub

And make sure the Sheets are actually called 'Goal Tracking' and '16-17M' (including spaces etc), if they are not, adjust the code accordingly but keep the parentheses.