VBA - Using Declared Range Variable in looping script across multiple sheets

[Lambrix]

I am trying to adjust the code so that it cycles through the sheets using a range variable rather than hard coding Range ("O4"). However, when I run the code, it works on the first sheet and then gives me a run-time error '1004' when it gets to "MyRange.Select" - Any ideas what I am doing wrong here?

        Sub AllWorkSheets()


        Dim ActSheet As Worksheet
        Dim Junction As String
        Dim BOC As String
        Dim Amount As String
        Dim MyRange As Range
        Set MyRange = ActiveCell

        '' Added the MyRange Variable to replace the hard coded Values below used in Amount and Range

        Sheets("Aggregate").Activate
        Set ActSheet = ActiveSheet
        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets

        If Left(ws.Name, 8) = "Junction" Or Left(ws.Name, 8) = "National" Then
        ws.Select

        ' Blocked and copied code while I test
        'Amount = Range("O4").Value
        'Range("O4").Activate
        'Amount = Range("O4").Value

        Amount = MyRange.Value
        MyRange.Select
        Amount = MyRange.Value

        BOC = ActiveCell.Offset(0, -12).Value
        Junction = ActiveCell.Offset(0, -14).Value
        ActSheet.Select
        Range("B1").Value = "BOCs"
        Range("A1").Value = "Junction Num"
        Cells(Rows.Count, 1).End(xlUp).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.Value = Junction
        ActiveCell.Offset(0, 2) = Amount
        ActiveCell.Offset(0, 1) = BOC

        End If
        Next

        End Sub

 

[JoeMo]

You are changing the active sheet with your ws.Select line so MyRange is no longer on the active sheet and therefore cannot be selected. You don't need all the selecting and activating - it just slows the execution down.

 

[Lambrix]

You are changing the active sheet with your ws.Select line so MyRange is no longer on the active sheet and therefore cannot be selected. You don't need all the selecting and activating - it just slows the execution down.

Thanks for your response Joe.

All the sheets are structured exactly the same way. I am trying to set a range variable based on my current active cell, and then use the range variable to pull that information using a loop on the remaining worksheets.

If the Range Variable is only valid on the activesheet, How would I grab this information on the remaining worksheets?

 

[JoeMo]

Move the Set MyRange = Activecell into the For each ws loop.

 

[Lambrix]

Move the Set MyRange = Activecell into the For each ws loop.

It stopped the error message but does not grab the correct range on the other sheets once it starts looping. For example, if I select cell "O5" on my active sheet, I would want to grab the information in "O5" on all the sheets.

How would I go about passing the correct range variable across all the worksheets? Would I need to use a public range variable? What do you suggest?

Thanks again.

 

[JoeMo]

Maybe:
Dim MyRangeAdr as string, MyRange as Range
Set MyRange = ActiveCell
MyRangeAdr = MyRange.Address(0,0)

Then inside your For Each Loop
set MyRange = ws.Range(MyRangeAdr)

 

[nuked]

You could put the address into a string.

Dim sRngAddress as String
sRngAddress = Activecell.Address
Sheets("Whatever").Range(sRngAddress) = "Yipee"