Macro runs on active sheet instead of specified

[drluke]

I am want to run a macro on a specific sheet by pressing a command button that is in another sheet. The code I have is:

Sub FormatData ()
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False

the rest of the code after that.

The problem is that the macro runs on the active sheet (ie the sheet the button is on). I've looked at all the previous posts about this but, as there seems to be a different answer every time this question is asked, I am very confused as to how to correct/change my code so that the macro runs on the correct sheet.

Any help would be appreciated.

 

[drluke]

There is a fair bit of recorded code to plow through to tidy up which I won't have time to do tonight but might have a go at it if I get bored during the week

But in the meantime it is as I said, you are setting up a variable and then not using it so all the code is acting on the activesheet.

All the time you are using Select and Selection then the sheet has to be the active sheet as Select can only be used on the activesheet.

In the meantime where you have put in the new code you are duplicating the task so change

Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False
Dim ShtName As String
ShtName = "NewData"


Sheets(ShtName).Activate

to just

Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False
ws.Activate

and if nobody else posts anything I will try and post some updated code during the week.

Thank you. Much appreciated - I am learning all the time