In Visual Basic for Excel how do I create a macro that navigates between two worksheets?

Question

I have three worksheets: Base, Mensal and Dif.
I need to create a macro that goes to a specific cell in the worksheet "Dif" and writes there the difference between a value that is writen in the worksheet"Base" and the worksheet "Mensal".I've been cracking my head over this but I can't find a way to do it!!!

Answer 1

Here is one ways which uses a macro function that you place into a module stored within the workbook. I used the default sheet names and called the function diffB7 (difference B7). You can obviously change these to what ever you want. To use the function and place the result in whaterver cell you want just add = diffB7() to that cell.



Public Function DiffB7() As Double



Dim col As Integer

Dim row As Integer

Dim x As Double

Dim y As Double

Dim z As Double



'default sheet names are being used

col = 2

row = 7



x = Sheet2.Cells(row, col)

y = Sheet3.Cells(row, col)



z = x - y



DiffB7 = z



End Function

Answer 2

Create a macro by using Microsoft Visual Basic

1.If the Developer tab is not available, do the following to display it:

1.Click the Microsoft Office Button , and then click Excel Options.

2.In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

2.To set the security level temporarily to enable all macros, do the following:

1.On the Developer tab, in the Code group, click Macro Security.





2.Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

Note To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.



3.On the Developer tab, in the Code group, click Visual Basic.

4.If needed, in the Visual Basic Editor, on the Insert menu, click Module.

Note Modules are automatically created for all sheets in the workbook.



5.In the code window of the module, type or copy the macro code that you want to use.

6.To run the macro from the module window, press F5.

7.In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when you finish writing the macro.





Source: Microsoft excel help

Answer 3

Try something like:





Sub CalcDiff()

Sheets("Dif").Range("C7").Value = _

Sheets("Base").Range("B7").Value - _

Sheets("Mensal").Range("B7").Value

End Sub