Possible to use ActiveSheet.name in formula? NOOB

[gaallen]

First, I believe the activesheet.name is a value that changes each time a new tab is selected? If I'm wrong there, the rest of this is not going to make sense. Please correct me if mistaken.

If Sheet3 has lots of data collected, I'd like to sort the data depending on which of the other Sheets (1 or 2) is active (selected). So I think I can sort based on the active sheet name, if I can just get that information to Sheet3.

Sheets 1 and 2 will use an offset to return the relevant information to them.

Unfortunately, my experience with VBA consists of recording macros and then modifying the resulting code to do what I need.

Please respond as if I don't know what I'm doing with VBA, I promise not to be offended........

Feel free to code a 3 sheet workbook sample if it's easier than explaining. I can learn by reverse engineering it.

I'm running XP Pro with Office 2007, by the way. Thanks.

 

[AlphaFrog]

You are correct about the ActiveSheet.Name. The rest of your question still doesn't make sense .

Do you want to sort Sheet3 when Sheet1 is selected and then resort Sheet3 differently when Sheet2 is selected?

If Yes, this SheetActivate event macro will run automatically when you make a sheet selection (click on a sheet tab)...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If Sh.Name = "Sheet1" Then
    
        'Sort sheet3 for sheet1 here
    
    ElseIf Sh.Name = "Sheet2" Then
    
        'Sort sheet3 for sheet2 here
        
    End If

End Sub

This code would have to go into the ThisWorkbook module.

There may be other better ways to achieve whatever end-result you are looking for, but you would have to give more specific details.

 

[gaallen]

It made sense in my head, but that doesn't count, does it? Anyhow, you understood the goal exactly, ie. Sheet1 selected => Sheet3 sorts, Sheet2 selected => Sheet3 sorts differently.

Your VBA was a cool solution, one that I hadn't considered. I'll try that route also.

If I end up just using sort functions on Sheet3, would it be possible to paste the active sheet name to a cell in Sheet3? The cell value should change to equal Sheet1.name when Sheet1 is active, Sheet2.name when Sheet 2 is active, etc....

Thanks for your guidance. I found several instances on this forum of people wanting to change tab names based on a cell value, but none that go the other way.

 

[AlphaFrog]

This will put the selected sheet name in cell A1 on Sheet3

Sub Sort_Me

    Sheets("Sheet3").Range("A1").Value = ActiveSheet.Name

    'Sort code goes here

End Sub

 

[gaallen]

Thanks for such a fast response AlphaFrog. You've provided me with 2 great solutions.

 

[Skublaka]

Hello, Can you guys help me out.

I have task to do a difficult thing at least as i see it.I am downloading huge date base that have 14 Columns in length A,B,C,D,E ..... and 500 rows ( example)
what i need to do is to create 7 sheets and named them specifically for example "OUT OF SERVICE " i did manage to find code

Sub copycolumns()
Dim lastrow As Long, erow As Long


lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 6) = "OUT OF SERVICE" Then
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 1)


Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 2)


Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)
End If
Next i


Application.CutCopyMode = False
Sheet2.Columns.AutoFit
Range("A1").Select


End Sub
The error is occurring when i try to write command to rename sheet 2 into "OUT OF SERVICE"
I also need to be able to update new sheet with similar data . Can someone help me out ?