copying data from multiple worksheets

gregh

New Member
Joined
Nov 10, 2005
Messages
7
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
Code:
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Try:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,539
Messages
5,572,786
Members
412,484
Latest member
deezina07
Top