I tested it and it produced identical output to J.E's routine. The only
difference would be if you have empty cells in B39 on any of the sheets.
Perhaps you do. Just like J.E's it rewrites the data in A3:A63.

--
Regards,
Tom Ogilvy


"ryguy7272" wrote:

I neglected to mention one important thing, I have all the names in the
workbooks listed in the summary sheet, from A3:A63. This is achieved with
the following macro:

Sub ListSheets()

Dim rng1 As Range
Dim I As Integer
Dim sh As Worksheet
Dim blnReplace As Boolean
Set rng1 = Range("A3")
For Each Sheet In ActiveWorkbook.Sheets
If (Sheet.Name) < "Summary" Then
blnReplace = False
rng1.Offset(I, 0).Value = Sheet.Name
I = I + 1
End If
Next Sheet
End Sub

Toms code is almost working for me! I know I didnt specify that I had an
array of data in A3:A63, but I'm mentioning it now. What do I have to add
now to get the copied/pasted data to shift down one row after it is
copied/pasted into the Summary sheet? I am guessing that it would be
something like

rng.Offset(A, 0).Value = sh.Name

But I tried this and that doesnt give me the results that I am seeking.
Any suggestions? Almost there!!

--
RyGuy


"Tom Ogilvy" wrote:

Sub ListData()

Dim rng As Range
Dim sh as Worksheet

For Each sh In ActiveWorkbook.WorkSheets
If lcase(sh.Name) < "summary" Then

set rng = worksheets("Summary").Cells( _
rows.count,2).End(xlup)(2)
if rng.row < 3 then _
set rng = worksheets("Summary").Range("B3")
sh.Range("B39:T39").copy
rng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
rng.offset(0,-1).value = sh.name
End if ' name < summary
Next sh
End Sub

--
Regards,
Tom Ogilvy



"ryguy7272" wrote:

Between using the macro recorder and recycling some code that I found on this
DG a while back, I tried to create a simple loop to select a range of cells
on each sheet in my workbook, and copy/paste the values into 'Summary'. I
dont want to copy the Range("B39:T39") from the €˜Summary, but I do want it
from all other sheets€¦.and then copy/paste it to the €˜Summary. Should be a
simple fix€¦I hope€¦


Code listed below; would someone please explain what I am doing wrong:

Sub ListData()

Dim A As Integer
Dim rng2 As Range
Set rng2 = Range("B3")

For Each rng2 In ActiveWorkbook.Sheets
If (Sheet.Name) < "Summary" Then

Range("B39:T39").Select
Selection.Copy

Sheets("Summary").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

rng2.Offset(I, 0).Value = Sheet.Name
A = A + 1
End If
Next rng2

End Sub


PS, sorry if this double-posts...I think I just got kicked out of my
original posting window...

--
RyGuy