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.

try

for each ws in worksheets
if ws.name<"Main" or ws.name <"Report" then
do your stuff
next ws.
--
Don Guillett
SalesAid Software

"Jig Bhakta" wrote in message
...
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.

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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jig Bhakta" wrote in message ...
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.

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.

--
Regards,
Tom Ogilvy

"Jig Bhakta" wrote:

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.