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
------------------------------------------------------------------------
jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
View this thread: http://www.excelforum.com/showthread...hreadid=488524

Any ideas?


--
jimbob
------------------------------------------------------------------------
jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
View this thread: http://www.excelforum.com/showthread...hreadid=488524

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

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
------------------------------------------------------------------------
jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
View this thread: http://www.excelforum.com/showthread...hreadid=488524

--

Dave Peterson

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

Yes, the cells are in the same locations on each page. So I would be
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 so
much for your help.


--
jimbob
------------------------------------------------------------------------
jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
View this thread: http://www.excelforum.com/showthread...hreadid=488524

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?


--
jimbob
------------------------------------------------------------------------
jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
View this thread: http://www.excelforum.com/showthread...hreadid=488524