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

 

[Georgiboy]

You can't select something that is not on the active sheet, you should not even need to activate the sheet.

Maybe the below will help:

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.Range("A2").Value = rngA2.Value
        End If
    Next ws
    
    Application.ScreenUpdating = True ' Enable screen updating
End Sub

 

[howard]

Thanks for the Help. I have sheets from BR1 sales to Southern Sales where if I select A2 on Sheet BR1 Sales, then all the sheets from BR1 Sales to Southern Sales must be on cell A2

When I select A2 on BR1 Sales then some other the other sheets are not on A2

 

[Georgiboy]

What is the aim, to select cell A2 on each sheet?

 

[Alex Blakenburg]

I am sure that @Georgiboy can help you with your code if you provide a clear explanation of what you are trying to achieve but in terms or addressing the specific question see if this helps.

    ws.Activate
    
    ws.Range("A2").Select
    ' or
    ActiveSheet.Range("A2").Select
    ' or
    ws.Range(rngA2.Address).Select
    ' or
    ActiveSheet.Range(rngA2.Address).Select

 

[dmt32]

When I select A2 on BR1 Sales then some other the other sheets are not on A2

not sure I fully follow what you want but see if this update to your code helps

Place this code in Your BR1 sheets code page

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

This code can go in standard module

Private 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("Southeern 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

this method gives option if needed to pass other selection addresses otherwise you probably could just hard code the A2 range in the selection.

Dave

 

[howard]

Thanks for the help Guys

When go to cell A2 on sheet BR1 Sales , I want all the sheets from BR1 Sales to Southern Sales to go to Cell A2

 

[Alex Blakenburg]

This code can go in standard module

Private Sub MoveSheetsToA2(ByVal Target As Range)

You would need to change it from Private to Public if you want to move it to a standard module.

When go to cell A2 on sheet BR1 Sales , I want all the sheets from BR1 Sales to Southern Sales to go to Cell A2

Your original code swapping out the select line inside the loop with one of my suggestions will do that, as will Dave's option as long as if you put it in a standard module you change it from Private to Public.
If neither of the 2 options are working can you be a bit more specific as to what it is doing.

PS: It seems inefficient to trigger this code on every change of selection. The selection won't be visible until you go to another sheets, so it would seem to make more sense to do in on a Worksheet_Deactivate() event.

 

[dmt32]

You would need to change it from Private to Public if you want to move it to a standard module.

Spotted the oversight after I posted but thanks for catch - much appreciated

Dave

 

[howard]

Thanks for the help Guys