Using VBA Running A Loop Thru Multiple Worksheets

Question

All,

I have a table setup in one workbook with formulas that I am extracting results into a separate workbook with 11 worksheets. All 11 worksheets are identical and as opposed to writing individual code for each, I wanted to see if there was a simple way to loop thru the worksheets and grab the correct data. Each worksheet has a specific row of data (i.e Worksheet1 will have B3:Y3. Worksheet2 will have B4:Y4 etc. etc.) Any help would be greatly appreciated for this novice. I have used "'"to show what I have attempted.

Here's my current code that works perfectly for worksheet 1, not so much for the other 10..

Sub Button1_Click()

'Dim wkSheet As WorkSheet

 SpendYTD = Sheets("Sheet1").Range("B3:B14")
 CCO = Sheets("Sheet1").Range("C3:C14")
 CustComp = Sheets("Sheet1").Range("D3:D14")
 WorkError = Sheets("Sheet1").Range("E3:E14")
'Cont_Change_%_Actual = Sheets("Sheet1").Range("F3:F14")
'Cont_Change_%_Target= Sheets("Sheet1").Range("G3:G14")
 Forecast_Actual = Sheets("Sheet1").Range("H3:H14")
 Forecast_Target = Sheets("Sheet1").Range("I3:I14")
 Attainment_Actual = Sheets("Sheet1").Range("J3:J14")
 Attainment_Target = Sheets("Sheet1").Range("K3:K14")
 Planned_Outage_Actual = Sheets("Sheet1").Range("L3:L14")
 Planned_Outage_Target = Sheets("Sheet1").Range("M3:M14")
 WorkProc_Error_Actual = Sheets("Sheet1").Range("N3:N14")
 WorkProc_Error_Target = Sheets("Sheet1").Range("O3:O14")
 NumWork_Error_Actual = Sheets("Sheet1").Range("P3:P14")
 NumWork_Error_Target = Sheets("Sheet1").Range("Q3:Q14")
 Control_Wires_Actual = Sheets("Sheet1").Range("R3:R14")
 Control_Wires_Target = Sheets("Sheet1").Range("S3:S14")

'Quarterly Findings

 Quality_Actual = Sheets("Sheet1").Range("T3:T14")
 Quality_Target = Sheets("Sheet1").Range("U3:T15")
 Ease_Bus_Actual = Sheets("Sheet1").Range("V3:V14")
 Ease_Bus_Target = Sheets("Sheet1").Range("W3:W14")
 Cust_Comp_Actual = Sheets("Sheet1").Range("X3:X14")
 Cust_Comp_Target = Sheets("Sheet1").Range("Y3:Y14")

 Workbooks.Open Filename:= _
    "Macintosh HD:Users:BG:Desktop:Scorecard.xlsx"
     ActiveWorkbook.Sheets("BRb").Activate

' For Each WorkSheet In ActiveWorkbook.Sheets

 ActiveSheet.Range("B6") = SpendYTD
 ActiveSheet.Range("B7") = CCO
 ActiveSheet.Range("F6") = CustComp
 ActiveSheet.Range("F7") = WorkError
'ActiveSheet.Range("E10") = Cont_Change_%_Actual
'ActiveSheet.Range("F10") = Cont_Change_%_Target
 ActiveSheet.Range("E11") = Forecast_Actual
 ActiveSheet.Range("F11") = Forecast_Target
 ActiveSheet.Range("E12") = Attainment_Actual
 ActiveSheet.Range("F12") = Attainment_Target
 ActiveSheet.Range("E13") = Planned_Outage_Actual
 ActiveSheet.Range("F13") = Planned_Outage_Target
 ActiveSheet.Range("E14") = WorkProc_Error_Actual
 ActiveSheet.Range("F14") = WorkProc_Error_Target
 ActiveSheet.Range("E15") = NumWork_Error_Actual
 ActiveSheet.Range("F15") = NumWork_Error_Target
 ActiveSheet.Range("E16") = Control_Wires_Actual
 ActiveSheet.Range("F16") = Control_Wires_Target

'Quarterly Results
 ActiveSheet.Range("E20") = Quality_Actual
 ActiveSheet.Range("F20") = Quality_Target
 ActiveSheet.Range("E21") = Ease_Bus_Actual
 ActiveSheet.Range("F21") = Ease_Bus_Target
 ActiveSheet.Range("E22") = Cust_Comp_Actual
 ActiveSheet.Range("F22") = Cust_Comp_Target

 ActiveWorkbook.Save
 ActiveWorkbook.Close

 'MsgBox WorkSheet.Name

 'Next


  ActiveWorkbook.Save
  ActiveWorkbook.Close


 End Sub

Answer 1

You can use an object variable for the worksheets like

dim ws as worksheet
set ws = thisworkbook.sheets("sheet1")

You can loop through all the sheets in a workbook with a for each loop

dim ws as worksheet
for each ws in thisworkbook.worksheets
    ws.dowork
next ws

All of your activesheet and/or sheets("sheet1") can be replaced with the ws variable.