For Loop Lower and Upper Bounds

Question

I got a question regarding the use of a For Loop using lower and upper bounds. The goal of the macro is to copy data from multiple sheets and paste into another workbook. The bounds are to be typed by the user in cells B3 as the lower bound and C3 as the upper bound. If B3 = 4 and C3=6 I would like the code to loop through sheets: 4 , 5 , 6. The sheets are named as the numbers only. "4" is labeled as "4" NOT "Sheet4".

Here is where I'm getting stuck. If I define the LB & UB with array command in the line below, I have no issues running through he FOR Loop.

sheetlist = Array("4", "5", "6")

However if I try to use the lo & hi as in the code below. the Macro jumps to the last sheet in the "Truck Log-East Gate-January.xlsx" regardless of the sheet name. It's like Worksheets(sheetlist(X)).Activate is not interrupting the lower and upper bound but rather just jumps to the last sheet.

Sub Refresh()
Dim lo As Long: lo = ActiveSheet.Range("B3")
Dim hi As Long: hi = ActiveSheet.Range("C3")
Dim sheetlist: sheetlist = Application.Transpose(Evaluate("row(" & lo & ":" & hi & ")"))
Debug.Print "~~> " & Join(sheetlist, ","), _
      vbNewLine & "Boundaries: " & LBound(sheetlist) & " To " & UBound(sheetlist)
'Loop Through sheetlist
For X = LBound(sheetlist) To UBound(sheetlist)

Windows("Truck Log-East Gate-January.xlsx").Activate

Worksheets(sheetlist(X)).Activate
    
    Range("A4:R4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Windows("Truck Racks RawData.xlsm").Activate
    Sheets("RawDataMacro").Select

    Range("A" & Rows.Count).End(xlUp).Select ' starts from the bottom of the worksheet and finds the last cell with data

    ActiveCell.Offset(1).Select ' moves cursor down one cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Next X

score 1 · Answer 1 · answered Feb 09 '21 at 14:29

Your array contains numbers, so if lo = 4 and hi = 6, then the first iteration of your loop is effectively:

Worksheets(4).Activate

which activates the 4th sheet from the left. What you want is effectively:

Worksheets("4").Activate

so you need:

Worksheets("" & sheetlist(x)).Activate

For Loop Lower and Upper Bounds

1 Answers1