I'm not sure I have it exactly the way you want it, but try:

Public Sub ListData()
Dim ws As Worksheet
Dim rCopy As Range
Dim rDest As Range

Set rDest = ActiveWorkbook.Worksheets("Summary").Range("B3")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "Summary" Then
rDest.Offset(0, -1).Value = ws.Name
With ws.Range("B39:T39")
rDest.Resize(1, .Columns.Count).Value = .Value
End With
Set rDest = rDest.Offset(1, 0)
End If
Next ws
End Sub



In article ,
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...