copying data from multiple worksheets

[gregh]

Hi,
I have a file that has many (approx 100) worksheets. On each of these sheets, there are five cells of data in cells A100-E100. I would like to extract this data from each sheet and put it in one sheet with 100 rows. I have been trying to write a macro that would loop through each sheet, but no luck so far.

Any suggestions?

Thanks

 

[Norie]

Perhaps something like this.

Dim wsNew As Worksheet
Dim ws As Worksheet
Dim I As Long

Set wsNew = Worksheets.Add
I=1
For Each ws In Worksheets
     If ws.Name<>wsNew.Name Then 

         ws.Range("A100:E100").Copy wsNew.Range("A" & I)
         I=I+1
     End If
Next ws

 

[Von Pookie]

Try:

Sub test()
Dim ws As Worksheet, i As Long

i = 1
For Each ws In ThisWorkbook.Worksheets
    'Replace "Extracted Data with the name of the sheet _
    the results will be copied to
    
    If ws.Name <> "Extracted Data" Then
        ws.Range("A100:E100").Copy Destination:= _
        Sheets("Extracted Data").Cells(i, "A")
        i = i + 1
    End If
Next ws

End Sub

 

[gregh]

you guys are awesome - it worked perfectly!!!

Thanks so much.