What did I learn? Excellent question! I tried to declare A as a variable,
and after running through the first iteration of the loop I though I had to
increment by one, or basically, A = A + 1. I thought this was a necessary
element of the loop. I guess JE McGimpseys code does all of this with the
For Each ws and Next ws. Also, I needed to declare a variable as Worksheet
and I needed to declare two variables as Range. In my code, I didnt declare
a variable as Worksheet, and only declared one variable as Range. I tried to
set my destination range as B3, and then tried to Offset by one with the I
variable, which was never declared. Im not sure what the following does:
rDest.Offset(0, -1).Value = ws.Name I put a tick mark in front of it, ran the
code, and it seemed to work fine. Also, Im not sure I fully understand the
following: rDest.Resize(1, .Columns.Count).Value = .Value I would have
thought it was something like rDest.Resize(1, 0).Value = .Value However,
when I tried this it failed, so I hit Ctrl Z. Is all of this, or any of
this, right? Im fascinated by this stuff. I know I still have a lot to
learn. Im trying to understand this particular concept so I can apply this
to many other types of scenarios; Im striving to resolve my own problems
and, of course, assist others when needed.
--
RyGuy


"Tom Ogilvy" wrote:

What did you learn?

--
Regards,
Tom Ogilvy


"ryguy7272" wrote:

THAT'S IT JE McGimpsey!!! That gives me the result I was looking for!!
Another day, another lesson learned!

Thanks so much!
--
RyGuy


"JE McGimpsey" wrote:

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
don€„¢t 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...