Transfer data from multiple worksheets

[jimbob]

I have a workbook with about 200 worksheets. I want to transfer the data
in a number of cells in each worksheet to a new worksheet. I would like
to do this automatically (not type in each entry). The data is not
contiguous...it's in different cells throughout the worksheet. The
transfer has to be done in order so that data for Wkst 1 is on one row,
data for Wksht #2 on the next and so on. I would then have new column
headings for the entrys. Any ideas? Thanks so much.

 

[jimbob]

Any ideas?

 

[Dave Peterson]

I think you'll have to post a few more details to get any real assistance.

If the cells are not contiguous, are they always in the same location (on each
sheet)?

Are there any sheets that should be avoided (besides the summary sheet)?

Maybe something like this will help...

Option Explicit
Sub testme()
Dim SumWks As Worksheet
Dim wks As Worksheet
Dim myAddr As Variant
Dim DestCell As Range
Dim iCtr As Long
Dim wCtr As Long
Dim cCtr As Long

myAddr = Array("a1", "b9", "C18")

Set SumWks = Worksheets("Summary")
With SumWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

For wCtr = 1 To Worksheets.Count
Set wks = Worksheets(wCtr)
If wks.Name = SumWks.Name Then
'do nothing
Else
'drop down a row
Set DestCell = DestCell.Offset(1, 0)
DestCell.Value = "'" & wks.Name
cCtr = 1
For iCtr = LBound(myAddr) To UBound(myAddr)
DestCell.Offset(0, cCtr).Value = wks.Range(myAddr(iCtr)).Value
cCtr = cCtr + 1
Next iCtr
End If
Next wCtr
End Sub

 

[jimbob]

Sorry I didn't respond earlier...been very busy.

Yes, the cells are in the same locations on each page. So I would b
exporting data for the same cells on every sheet to a new worksheet.

So data from H6 on WkSht 1 would go to cell A1 on New WkSht
data from H6 on WS 2 would go to A2 on New Wksht and so on. Thanks s
much for your help

 

[jimbob]

Still looking for help with this. I have a number of cells that I need
to export to another spreadsheet. The cells are not contiguous or in
columns. Any ideas?