Button On Different Worksheet Than Where I Want The Code To Run

[Yoandsm]

Hello All,

Thank you in advance for any help that you can provide. I'm hoping that my questions is relatively basic, but for some reason I cannot find a solution to my problem online.

I want to have an Overview worksheet that has buttons to run certain macros on certain sheets. For example, I would like to push Button1 on the Summary Worksheet, and have it run my Macro on the "Import" worksheet.

Is this as simple as I am hoping?

Thank you!

[Mumps1]

You can do that by having the macro reference the "Import" worksheet. For example, if you assign the macro below to your button, it will copy A1 from "Import" to A1 of the sheet with the button.

Sub test()
    Sheets("Import").Range("A1").Copy Range("A1")
End Sub

[Yoandsm]

Thank you for the reply! Maybe I am misunderstanding you but I don't want the data from Import to be copied to the worksheet with the buttons. I want the macro to run on the Import worksheet, leave it on the import worksheet, but have the buttons on another worksheet. Or was I misunderstanding you?

[Mumps1]

It would be helpful if you could attach a copy of your file including the buttons and explain in detail what you want to do referring to specific cells, rows, columns and sheets.

[Mumps1]

@Richard Buttrey
Hi Richard. Could I ask why you shouldn't use the sheet tab name? I always thought that using the tab name makes it much easier to identify which sheet is being referenced because most often the tab name is an indicator of the sheet contents or purpose. In a macro that has many sheet references I think that this could be an advantage.

[Richard Buttrey]

Hard coding the sheet name in a macro puts you at the whims of a user who might change the tab name. A macro will fall over if this happens.

It's always better to use the VB code name. You can if you wish change the VB CodeName to BE the same as the tab name if you wish.

I suspect many of us don't bother to change the VB code name, I can't ever remember doing so after my early learning experience. In the VBE when you need to refer to the VB code name it's always visible, along with the tab name in the Project pane so no information is unavailable when you're coding.

Not so bad if you are the only user and are aware of this potential hazard but I've lost track of the times in my early days before I realised the significance, of clients complaining that 'my workbook macro isn't working' only to establish that someone had changed the tab name

[Mumps1]

Thank you for your feedback, Richard. You have made an excellent point.

[Richard Buttrey]

Yes. But a common mistake is to fail to fully identify and qualify any ranges you are wanting to use in your macro.
If you start a macro with a button on say Sheet1 and the macro is intended to refer to cells on Sheer2, then code like

Range("E1:J20").Copy

will simply copy E1:J20 on sheet1. You should use

Sheet2.Range("E1:J20").Copy

And in VBA get into the habit of using the VBA Sheet code name NOT the sheet tab name, or worse the sheet index value (i.e. its position in the series of tab names)
i.e. Sheet1. not Sheets("Sheet1").