Simple looping question

  • Thread starter Thread starter James8309
  • Start date Start date
J

James8309

Hi everyone,

I have about 20 different worksheets in the same workbook. I also have
a main macro for the workbook. I know how to loop the macro through
all the worksheets. However how do I loop the sheets I want only?

i.e. Loop sheets("A"), sheets("B") and Sheets("Z") only, Not running
particular part of marco for other sheets.


Thanks for your help
 
dim arr,x
arr=Array("A","B","D","X")

for x=lbound(arr) to ubound(arr)

with thisworkbook.sheets(arr(x))
'do stuff
end with

next x


Tim
 
dim arr,x
arr=Array("A","B","D","X")

for x=lbound(arr) to ubound(arr)

    with thisworkbook.sheets(arr(x))
        'do stuff
    end with

next x

Tim









- Show quoted text -
Click to expand...


In the middle of my code, I have this part where it does sumif.
Currently It only does it for one sheet. I just want to specify sheets
that will perform the code below (e.g. Sheet"A" and Sheet"B"). I tried
the code above but seems like it is not working.


Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim FNAME As String
Dim SUMREF As String
Dim COLCOUNT As Long

MYPATH = "C:\My Documents\"
LR = Range("A" & Rows.Count).End(xlUp).Row
Set ThisSht = Workbooks("Macro for Report.xls").ActiveSheet

'column B = 2

For i = 2 To 41
COLCOUNT = i
With ThisSht
FNAME = MYPATH & .Range("A1").Value & "\" & _
Year(.Cells(5, COLCOUNT).Value) & "\" & _
Format(.Cells(5, COLCOUNT).Value, "MMM YY")
Debug.Print FNAME
FNAME = FNAME & ".XLS"
Set WB = Workbooks.Open(Filename:=FNAME)
Debug.Print FNAME

For Each CELL In .Range(.Cells(6, COLCOUNT), .Cells(LR, COLCOUNT))
SUMREF = .Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 33
CELL.Value = Application.WorksheetFunction. _
SumIf(WB.Sheets("Sheet1").Range("H:U"), _
SUMREF, WB.Sheets("Sheet1").Range("U:U"))
Next CELL
WB.Close
COLCOUNT = COLCOUNT + 1
End With
Next
 
Back
Top