Return to sheet where Macro was Launched

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,782
Office Version
  1. 365
Platform
  1. Windows
I have a VBA code that is launched from a userform. It basically goes to a sheet named "HOURS" runs a Filter and then prints. My problem is that I have my program setup to where I can launch the userfrorm (UserForm46) from a number of different sheets say Sheet (1), Sheet (2) or Sheet (3)... When the print macro is run from the userfrom it leaves me on the Sheet titled HOURS. I want to be able to go back to the sheet I lanched it from. Note that it could be from a number of different sheets to its not as easy as Sheets("Sheet (1)").Select . Sheet (1) may not be where its was launched from.

Any suggestions?

Thanks
Greg in Orlando
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Greg

Can we see the code?

Are you activating/selecting sheets?

If you are, you don't need to and focus will stay with the worksheet the macro was launched from.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there Greg, welcome to the board!

You can do this by setting a variable at the beginning of your code ..

Code:
Sub MycodeMacro()
    Dim ws as worksheet
    Set ws = Activesheet
    '..
    '.. your code
    '..
    ws.Activate
End Sub

I'm not really sure why you are changing sheets in the first place though. It gives me a suspicion that your code may not be as efficient as it could be. Would you mind posting your code?
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
use something like this

Code:
Sub SetName()
Dim ws As Worksheet
Set ws = ActiveSheet
MsgBox "Hello, you opened the workbook named " & ws.Name & "."

End Sub

You'll have to modify as needed
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,782
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the help

Here is the code;

Private Sub CommandButton3_Click()
Sheets("HOURS").Activate
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
End Sub

This is launched from a userform. The userform is activated from various sheets
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Like I said no need for selecting/activating.
Code:
Private Sub CommandButton3_Click()
    With Sheets("HOURS")
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=1, Criteria1:="TRUE"
        .PrintOut Copies:=1, Collate:=True ' , Preview:=True
        .Range("A1").AutoFilter Field:=1
    End With
End Sub
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,782
Office Version
  1. 365
Platform
  1. Windows
THANKS FOR ALL THE HELP

USING

Sub SetName()
Dim ws As Worksheet
Set ws = ActiveSheet
MsgBox "Hello, you opened the workbook named " & ws.Name & "."

End Sub


DID THE JOB. THANKS TO ALL!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,109
Members
412,441
Latest member
kelethymos
Top