How do we connect them so that after the c wash and rinse (copy and paste) runs it automatically runs the "TS Game 3_50 COUNTERS-1.xlsm" and then back to the copy and paste?
Without going into Error Checking, Make sure both books are Open. Note that Any Macro can check for open books and open them if needed, But why don't we wait until the code is working with that for now.
Real basic, 'cuz I have to leave now.
Put this in a Standard Module in TS Game 3_50 COUNTERS-1.xlsm
Option Explicit
Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x) 'The LBound of x is 0 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
If (x(i, 1)) = "Game" Then j = j + 1
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)
End With
End If
Next i
Workbooks("TS 4+ BB Game 3 (Macro Numbers Input)-1.xlsm").NumbersGrabber
End Sub
And this in a Standard Module in "TS 4+ BB Game 3 (Macro Numbers Input)-1.xlsm"
Option Explicit
Sub NumbersGrabber()
Dim InputSht As Worksheet
Dim DrnNumSht As Worksheet
Static NextRowToUse As Long 'maintains State between calls as long as Workbook is open. With caveats.
Set InputSht = Workbooks("TS Game 3_50 COUNTERS-1.xlsm").Sheets("Input")
Set DrnNumSht = Sheets("Drawn Numbers")
If NextRowToUse = 0 Then NextRowToUse = DrnNumSht.Cells(Rows.Count, "I").End(xlUp).Row
If NextRowToUse = 1 Then Exit Sub
DrnNumSht.Cells(NextRowToUse, "I").Resize(, 7).Copy 'Resize(0 Rows, 7 Columns)
InputSht.Range("I49").Resize(, 7).Insert shift:=xlShiftDown
NextRowToUse = NextRowToUse - 1
'Run the etert Procedure
'Workbooks("TS Game 3_50 COUNTERS-1.xlsm".ertert '<-------- UnComment after testing
End Sub
Make sure there is only one sub with the same name in any Workbook.
Notre the Workbook is specified in each Call to the other routine.