How do you get a macro to reference the worksheet it is being run

  • Thread starter Thread starter Michelle D
  • Start date Start date
M

Michelle D

How do you get a macro to reference the worksheet it is being run from?

How do you write in the macro formula "take data from sheet macro is being
run from" (macro is being run from a submit button on a sheet - but I would
like to make a template and have lots of similiar sheets so I need it to
identify where it is being run from - not reference the template)

Hope this makes sense - if not please ask me to clarify - I have posted a
few similiar questions - but not had a single answer - if I am going about it
the wrong way, please let me know.

Michelle
 
How do you get a macro to reference the worksheet it is being run from?

How do you write in the macro formula "take data from sheet macro is being
run from" (macro is being run from a submit button on a sheet - but I would
like to make a template and have lots of similiar sheets so I need it to
identify where it is being run from - not reference the template)
Click to expand...

Since the button is going to be on the ActiveSheet, all you need to do
is use that as a reference. You can also store it as a variable if you
need to, like so:

Private Sub CommandButton1_Click()

Dim wks As Excel.Worksheet

Set wks = ActiveSheet

'Do other stuff

MsgBox wks.Name

Set wks = Nothing

End Sub
 
ThisWorkbook.ActiveSheet.Name
will return the active sheetname of the workbook where the macro resides

MsgBox ActiveWorkbook.ActiveSheet.Name
will return the currently active sheetname of the active workbook

''Try the below macro
Sub Macro()
Dim wb As Workbook
Dim ws As Worksheet

'Referencing the workbook where the macro resides
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
MsgBox ws.Name

'Referencing the active activeworkbook
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
MsgBox ws.Name
End Sub

If this post helps click Yes
 
Back
Top