For...Next Loop

[Guest]

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...

 

[PCLIVE]

This seems like some type of test or something. There are a few areas that
seem like errors. If I understand correctly, you want to loop through all
the sheets (except Summary) and copy a specific range to the Summary sheet.
I've added some corrections to the code...see comments in those lines.

Sub ListData()
Dim A As Integer 'This seems to have no purpose
Dim rng2 As Range 'This doesn't seem correct for WorkSheets
Set rng2 = Range("B3") 'This doesn't seem correct for WorkSheets

For Each rng2 In ActiveWorkbook.Sheets 'Because of the Dim above, this
doesn't seem to work
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 'Is this a type "I" since there
is no variable set. Should it be 1?
A = A + 1 'This seems to have no purpose
End If
Next rng2
End Sub


**********I would use the following:
I just realized, I need to know more about what this is supposed to do.
Even though it doesn't give errors, some cells are overwriting each other.

Sub ListData()

Dim A As Integer

For Each ws In ActiveWorkbook.Sheets
If ws.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

ActiveCell.Offset(A, 0).Value = ws.Name
A = A + 1
End If
Next ws

End Sub

 

[Guest]

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

 

[Guest]

Assuming that "Summary" is the first sheet in the workbook:
I am not sure about the rng2.Offset line.

Sub ListData()

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

A = 2
For Each A In ActiveWorkbook.Sheets
With Cells
Sheets(A).Select
Range("B39:T39").Copy

Sheets("Summary").Columns("B:B").Select
Set rng = .Range(.Cells(3,2),.Cells(3,2)).End (xlDown)
rng.Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

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

End Sub

Pops Jackson

 

[JE McGimpsey]

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

 

[Guest]

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

Tom’s code is almost working for me! I know I didn’t 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 doesn’t give me the results that I am seeking.
Any suggestions? Almost there!!

 

[Guest]

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

Thanks so much!

 

[Guest]

What did you learn?

--
Regards,
Tom Ogilvy

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

Thanks so much!

 

[Guest]

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.

 

[Guest]

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 McGimpsey’s 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 didn’t 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. I’m 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, I’m 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? I’m fascinated by this stuff. I know I still have a lot to
learn. I’m trying to understand this particular concept so I can apply this
to many other types of scenarios; I’m striving to resolve my own problems
and, of course, assist others when needed.