For next loop with array and using a variable in range().select

[notmeiquit]

My boss has a complex Excel file he works with. Has multiple sheets that need to equal the same value across multiple sheets. Sheet names are Q1, Q2, Q3, R1, R2, R3 and R4 (in that order).
Q1 is the starting sheet. If a change is made on a specific cells in Q1 the value will be the same in Q2, Q3 and so on all the way to R4. This is accomplished with the simple = Prior sheet
formula in the cells.

When working in the sheets, if changes are made, lets say in Q3 by replacing the = formula (in a cell) with a hard value that is ok, R1, R2 and so on will pick up on the change and carry it forward.

I am struggling with creating VBA code that will loop through the cells and replace the changed cells with the = formula to equal the prior sheets corresponding cell. (Resetting every sheet for the next time it is used). Most of the cells are not next to another cell (like a range) so making an array with plugged cells to change is the only way.

MY ultimate goal would be to have the Array value, The actual cell location (i have in the array), [see array below] used as a variable in the range().select. This will keep the code to a minimum. My web searching has giving me no answers. I am close but I get run time error 1004 Method 'Range' of object'_Global' Failed

In the code below I only put a few cell locations that need to be updated. I will have about 30 or 40 cells to update. Any help would be greatly appreciated.

Thanks for your time.

Sub RepairSheets()

Dim indexArr As Variant
Dim i As Long
  
   indexArr = Array("B3", "D3", "J3", "N3")
   
For i = LBound(indexArr) To UBound(indexArr)
    
    Sheets("R4").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='R3'!RC"
    Sheets("R3").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='R2'!RC"
    Sheets("R2").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='R1'!RC"
    Sheets("R1").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='Q3'!RC"
    Sheets("Q3").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='Q2'!RC"
    Sheets("Q2").Select: Range(indexArr).Select: ActiveCell.FormulaR1C1 = "='Q1'!RC"

Next i

End Sub

 

[My Aswer Is This]

Are you saying for example?

You have a Master sheet named "Alpha"

And if you change the value in Range "A1" of sheet named "Alpha"
You want this same value entered into the same cell in Many other sheets?
If so a Vba script could do this.

If this is what you want give me the sheet name of the master sheet and all the ranges, we are dealing with.
And the vba script would enter the value and not ta formula.

 

[StephenCrump]

My boss has a complex Excel file he works with. Has multiple sheets that need to equal the same value across multiple sheets.

I'd say unnecessarily complex! It would make much more sense to have all the inputs in one place, on one worksheet, and have other worksheets refer to this single source.

But if you do want to implement this ...

The code below assumes all formulae are set up to start with. As cells are changed, the value is copied to Q1, and the formula reinstated.

'In ThisWorkbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rng As Range, r As Range
    Dim N As Variant
    Dim s() As String, rngToCheck As String
   
    rngToCheck = "B3,D3,J3,N3"
    s = Split("Q1,Q2,Q3,R1,R2,R3,R4", ",")
    N = Application.Match(Sh.Name, s, 0)
   
    If IsNumeric(N) Then
        If N > 1 Then
            Set rng = Intersect(Sh.Range(rngToCheck), Target)
            If Not rng Is Nothing Then
                Application.EnableEvents = False
                For Each r In rng
                    Worksheets(s(0)).Range(r.Address).Value = r.Value
                    r.FormulaR1C1 = "='" & s(N - 2) & "'!RC"
                Next r
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

 

[notmeiquit]

Are you saying for example?

You have a Master sheet named "Alpha"

And if you change the value in Range "A1" of sheet named "Alpha"
You want this same value entered into the same cell in Many other sheets?
If so a Vba script could do this.

If this is what you want give me the sheet name of the master sheet and all the ranges, we are dealing with.
And the vba script would enter the value and not ta formula.

Let me give you a little more info.


We use these sheets for a Race (Car Race). In one event we can do up to 7 Races in total over a few days. This is why it has Q1, Q2, Q3, R1, R2, R3, and R4. First 3 are qualifying and last 4 are the Race. Each time down the track information is stored in 1 Sheet starting with Q1. All editable cells are carried forward to the next sheet. When the next run down the track is taken, all the information from Q1 is already in Q2. Any changes made in Q2 will already be in Q3 all the way up the sheets to R4. The need for switching tabs is removed and time is saved.

I was tasked with a few challenges.

1. How to move the last race information, the last run for the event, back to Q1 for the next outing. For example, if only 3 runs were made during the event, then Q3's "data" would be moved back to Q1 sheet. (This was easy. I don't need help I got VBA code for this, and it works)

2. If any changes to tab names are made, they need to be corrected. For example, if only 2 qualifying runs are made then Tab3 would become R1 and up the chain. Needed an easy way to update tabs and tab colors. Any tabs not used in an outing get blacked out. (That was a fun challenge, but I got VBA code for that to work. I don't need help)

3. After, for example if 5 runs are made on an outing, then R2's data is moved back to Q1 tab. "DONE" Then I needed a way to make all sheets refer to the prior sheet. R4 = R3, R3 = R2, R2 = R1, etcetera back to Q1. This will untimely make every sheet equal to R2's data when R2 is moved to Q1 and all sheets equal down the chain Starting with R4 backwards. I can make a tone of "this sheets cell" = "Prior sheet cell" reference. Then I thought, what if I can make a loop that has an array of cell references that repeats the task. Otherwise, I will have 30 cells times 6 TABS of code to accomplish this LOL

# 3 is my problem child otherwise 200 lines of code to do a simple task. This code works but yikes

Sheets("R4").select: Range("D6").Select: ActiveCell.FormulaR1C1 = "='R3'!RC"

 

[notmeiquit]

I'd say unnecessarily complex! It would make much more sense to have all the inputs in one place, on one worksheet, and have other worksheets refer to this single source.

But if you do want to implement this ...

The code below assumes all formulae are set up to start with. As cells are changed, the value is copied to Q1, and the formula reinstated.

'In ThisWorkbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rng As Range, r As Range
    Dim N As Variant
    Dim s() As String, rngToCheck As String
  
    rngToCheck = "B3,D3,J3,N3"
    s = Split("Q1,Q2,Q3,R1,R2,R3,R4", ",")
    N = Application.Match(Sh.Name, s, 0)
  
    If IsNumeric(N) Then
        If N > 1 Then
            Set rng = Intersect(Sh.Range(rngToCheck), Target)
            If Not rng Is Nothing Then
                Application.EnableEvents = False
                For Each r In rng
                    Worksheets(s(0)).Range(r.Address).Value = r.Value
                    r.FormulaR1C1 = "='" & s(N - 2) & "'!RC"
                Next r
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

I placed the code in my sheets and not sure how to run it. I am a novice at doing code. The internet is my friend on solving most of my questions. So please bare with me as I am not sure how this code is called. It looks like it runs when the page is updated. I think if I am wrong so sorry. But if you could look at the reply I made to the previous question I was given it will explain in depth what is actually going on an what I ultimately need. I need code I can run on demand with a button press. I will be stringing multiple scripts together to simplify every task. When you only have 1 hour and 45 minutes to get a car ready for a race (between races) every minute not fusing with an excel sheets is a minute put into getting car ready for a race.

Thank you for all the time you put into writing that up for me. I'm sure we can make it work.... again THANKS!!

 

[notmeiquit]

I found an answer for what I need. This is the code that works for making a sheet cell equal any other sheet cell with an array
Thanks all for your time.

Sub RepairSheets()
    Dim indexArr As Variant
    Dim i As Long
    Dim ws As Worksheet
    Dim cellAddress As String
    indexArr = Array("B3", "D3", "J3", "N3")
    For i = LBound(indexArr) To UBound(indexArr)
        cellAddress = indexArr(i)
        Set ws = Sheets("R4")
        ws.Range(cellAddress).Formula2 = "='R3'!" & cellAddress
        Set ws = Sheets("R3")
        ws.Range(cellAddress).Formula2 = "='R2'!" & cellAddress
        Set ws = Sheets("R2")
        ws.Range(cellAddress).Formula2 = "='R1'!" & cellAddress
        Set ws = Sheets("R1")
        ws.Range(cellAddress).Formula2 = "='Q3'!" & cellAddress
        Set ws = Sheets("Q3")
        ws.Range(cellAddress).Formula2 = "='Q2'!" & cellAddress
        Set ws = Sheets("Q2")
        ws.Range(cellAddress).Formula2 = "='Q1'!" & cellAddress
    Next i
End Sub