Bypassing Worksheets when scanning worksheets for data

[Guest]

Hi have a workbook with 5 worksheets. The first one is 'Main', the Second is
'Report' and the 3-5 are 'ABC', '123', 'POG'.

I want to write VBA code that looks at all spreadsheets in the workbook
except 'Main' and 'Report' and then copy data from cell A2 and B5:B10 from
each of these worksheets (data is in the same location for each worksheet)
into the 'Report' worksheet's cell A1 through to G1 (for the sheet 'ABC'), A2
through to G2 (for the sheet '123'), A3 through to G3 (for the sheet 'POG'),
etc...

How can I do this?

Thanks.

 

[Don Guillett]

try

for each ws in worksheets
if ws.name<>"Main" or ws.name <>"Report" then
do your stuff
next ws.

 

[Ron de Bruin]

You can enter all three sheet names in the array (only three)
For Each sh In Sheets(Array("ABC", "123", "POG'"))

Sub test()
Dim rnum As Long
Dim sh As Worksheet
rnum = 1
For Each sh In Sheets(Array("ABC", "123", "POG"))
sh.Range("A2").Copy Sheets("Report").Cells(rnum, 1)
sh.Range("B5:B10").Copy
Sheets("Report").Cells(rnum, 2).PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
rnum = rnum + 1
Next
End Sub

 

[Guest]

Sub AAA()
Dim v as Variant, j as Long, i as Long
Dim sh as Worksheet
v = Array("ABC","123","POG")
j = 0
for i = lbound(v) to ubound(v)
set sh = worksheets(v(i))
j = j + 1
With worksheets("Main")
.cells(j,1).Value = sh.Range("A2")
.cells(j,2).Resize(1,6).Value = _
Application.Transpose(sh.Range("B5:B10").Value)
End with
Next i
end Sub

This gets the value from those cells.