Private Sub to Go to A2

[howard]

I have tried to write code to Move to A2 from Sheet BR1 Sales to Southern Sales when A2 is select on sheet BR1 Sales

When I select A2 on sheet Br1 Sales, I get "select method of class failed:

It would be appreciated if someone could kindly amend my code

rngA2.Select ' Select cell A2

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        MoveSheetsToA2
    End If
End Sub

Private Sub MoveSheetsToA2()
    Dim ws As Worksheet
    Dim startSheet As Worksheet
    Dim endSheet As Worksheet
    Dim rngA2 As Range
    
    Set startSheet = ThisWorkbook.Sheets("BR1 Sales")
    Set endSheet = ThisWorkbook.Sheets("Southeern Sales")
    Set rngA2 = startSheet.Range("A2")
    
    Application.ScreenUpdating = False ' Disable screen updating for better performance
    
    ' Move all sheets from startSheet to endSheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Index >= startSheet.Index And ws.Index <= endSheet.Index Then
            ws.Activate ' Activate the sheet
            rngA2.Select ' Select cell A2
        End If
    Next ws
    
    startSheet.Activate ' Activate the startSheet
    
    Application.ScreenUpdating = True ' Enable screen updating
    
    rngA2.Select ' Select cell A2
End Sub

 

[Alex Blakenburg]

Would you mind just clarifying what you ended up doing ?

 

[howard]

Hi Alex

I copied the code below in sheet BR1 Sales code page

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$2" Then MoveSheetsToA2 Target
End Sub

I copied this in to the standard Module

 Public Sub MoveSheetsToA2(ByVal Target As Range)
    Dim ws              As Worksheet
    Dim startSheet As Worksheet, endSheet As Worksheet

    Set startSheet = Target.Parent
    Set endSheet = ThisWorkbook.Sheets("Southern Sales")
  
    ' Disable screen updating for better performance
    Application.ScreenUpdating = False
  
    ' Move all sheets from startSheet to endSheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Index >= startSheet.Index And ws.Index <= endSheet.Index Then
            ws.Activate ' Activate the sheet
            ws.Range(Target.Address).Select
        End If
    Next ws
  
    ' Activate the startSheet
    With startSheet
        .Activate
        .Range(Target.Address).Select
     End With
   
     ' Enable screen updating
    Application.ScreenUpdating = True

End Sub

 

[Alex Blakenburg]

Thanks for clarifying. I don't think you had marked Dave's suggestion as the solution at the time or perhaps I just missed it .
Glad it is working for you now.