If cell selected, go to the worksheet with the same name

[Rediska]

Hi Excellians, long time no see!

Please help with the macro inquest:
When specific sell is selected on one sheet, macro will activate worksheet named with a cell text.
Example:
cell A1 contains "Sheet1",
cell A2 contains "Sheet2", etc.

When user selects A2, macro will activate (go to) worksheet "Sheet2", when user selects A1, macro will activate worksheet "Sheet1".
If cell empty, no action.

There is no need to create worksheets, they are already created.

Thanks!

 

[Chihiro]

Why macro? You could just use Hyperlink function.

This will create hyperlink which will jump you to cell A1 of "Sheet2" (replace with your actual sheet name).
=HYPERLINK("#"&"Sheet2!A1","Sheet2")

If you really must use Macro... below goes into your worksheet's module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Len(Target.Value) > 0 Then Sheets(Target.Value).Activate
End If
End Sub

 

[Chihiro]

? Not sure what you mean by that.

Typically event code should reside in appropriate module that triggers event.



There are pitfalls to having event codes in standard module.
https://stackoverflow.com/questions/20273724/excel-vba-how-to-trigger-an-event-from-code

 

[Rediska]

Thanks, code is working perfectly on its own,
Now I need to call it from a public sub (macro) - there more functions to add after this private sub is called, such as copy and past some data on active worksheet, remove duplicates and so on.
Thanks!

 

[Rediska]

Hi again,

Sorry, but I can't figure out a syntax to call this private sub from public sub:
these giving me an error:
-Application.Run "Sheet1.Worksheet_SelectionChange"
-Application.Run "Sheet1.Worksheet_SelectionChange()"
-Application.Run "Sheet1.Worksheet_SelectionChange(ByVal Target As Range)

Please help! I am sure it's simple for y, but I am lost
Thanks!

 

[Chihiro]

You can't call Private Sub from another Module.

You'll need to give more detail on scope of your entire project. Best bet is to trigger the Public Sub from Private sub.

 

[Rediska]

Thanks,

This is the project scope:
Production data reports for the specific time period are dumped into worksheets of a stats template. The stats summary page is set up so that when next new report is copied into new worksheet, worksheet' name is entered in column B on the summary page, and Sub gets activated.
Sub should

1. go to the worksheet named in the cell of column B,
2. select original report & copy all to the right (same worksheet)
3. remove duplicate records from the report's copy
4. back to the all reports stats summary page

Your private sub is perfectly executing #1, and I was trying to create pub sub for #2 to #4:

Sub ProcessReport()

  ActiveSheet.Select
 
'select original report & copy all to the right:
 
  Columns("A:Y").Select
  Selection.Copy
  Range("AA1").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Application.CutCopyMode = False
 
' remove duplicate records from the report's copy:

  Columns("AA:AY").Select
  ActiveSheet.Range("$AA$1:$AY$2500").RemoveDuplicates Columns:=1, Header:= _
  xlNo
 
'go back to the all reports stats summary page:

  Sheets("STATS").Select
  Range("C4").Select

End Sub

but it seems I can't put it all together.
To get my stats right, I need both reports - one complete and another with unique records.

 

[Chihiro]

Can you try calling the public sub from the private sub?

Other than that, I'll need sample workbook that mirrors your data set (with sanitized info) to help you further.

 

[Rediska]

I am trying, but getting syntax wrong.
How can I call Sub ProcessReport() from
Private Sub Worksheet_SelectionChange(ByVal Target As Range) as above?
Thanks

 

[Chihiro]

First, replace all of "ActiveSheet" with specific Worksheet reference (something like below).

Sub ProcessReport()
'select original report & copy all to the right:
With Worksheets("Yourworksheetname")
    .Columns("A:Y").Copy
    .Range("AA1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
' remove duplicate records from the report's copy:

    .Range("$AA$1:$AY$2500").RemoveDuplicates Columns:=1, Header:=xlNo
End With
'go back to the all reports stats summary page:

  Sheets("STATS").Range("C4").Select

End Sub

Then you'd use following line in appropriate part of Private Sub.

Call ProcessReport

 

[Rediska]

That's the problem - I can't specify the worksheet name, it is a selected cell on the stats and =Target.Value (see 1-st sub above)
Every time I enter new sequence worksheet name in column B, sub should go into that worksheet and do the copying (if I enter "Sheet1" into B10, it should go to worksheet Sheet1, activate it and do the job, if I enter "test" into B11, it should go to worksheet Test, activate it and do the same job).

 

[Rediska]

 

[Chihiro]

So just replace "YourworksheetName" with... Target.Value like below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Len(Target.Value) > 0 Then
        With Worksheets(Target.Value)
            .Columns("A:Y").Copy
            .Range("AA1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
            .Range("$AA$1:$AY$2500").RemoveDuplicates Columns:=1, Header:=xlNo
        End With
        Sheets("STATS").Range("C4").Select
    End If
End If
End Sub

 

[Rediska]

disregard: Sorry, doesn't work - doesn't activate/select sheet and/or copying.
It works PERFECTLY!!! I had to replace A:A with B:B

 

[Chihiro]

As I said, upload sample workbook, if you need further help.

In general, you don't need select/activate worksheets/ranges in VBA.