Quick Question on This Code

[Guest]

I'm using this code to copy the values in the last row of column D on each
Worksheet in my workbook (there are 10 sheets) and coping the numeric value
onto a worksheet called Summary. The thing is neither the WS titles or the
totals are populating the Summary document. What happens is the text "Sheet
1" populates cell A4, "Sheet 2" populates cell A5 and lastly "Sheet 3"
populates cell A6 of the Summary sheet, what would cause this to happen. I
just can't figure it out. Thanks

Sub SummaryTotals()

Dim c As Integer
Dim sht As Worksheet

c = 4
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Summary" Then
With Sheets("Summary")
..Cells(c, 1).Value = sht.Name
..Cells(c, 2).Value = sht.Cells(Rows.Count, 4).End(xlUp).Value
End With
c = c + 1
End If
Next sht

End Sub

 

[Leith Ross]

Hello SITCFanTN,

The problem is not the code rather it is a misunderstanding of the
..Cells() property. The value "C" in your code was meant to refer to
"columns". You mention that column "D" (represented by the number 4) is
where the data is to copied.

Cells Property Example:
..Cells(rows, columns)

You have this reversed...
..Cells(C, 1)

So, you are referencing Row 4, Column 1 or A4. Switch it to .Cells(1,
C) and it will start copying the data to D1.

Sincerely,
Leith Ross

 

[Guest]

Thanks Leith but this still isn't working for me. The code isn't bringing in
the sheet title or the dollar value in the last cell of column D. When I
changed the code it as you suggested, it still names the worksheets Sheet1,
Sheet2 and Sheet3 but now they appear in row 1 of the summary document and
the total at the bottom of column D on the worksheets is not being brought
into the summary sheet. Do you have any other suggestions? Thank I
appreciate you taking the time to look at this.

 

[Dave Peterson]

I would think you'd want:

For Each sht In ThisWorkbook.Worksheets
(instead of .sheets)

But your code worked fine for me.

And if you're running your code from a different workbook, you may want to use
Activeworkbook.worksheets.

Thisworkbook is the workbook with the code--which may not be the one that you're
looking at in excel.

 

[Guest]

Thanks Dave, that was it, the code works perfectly now. Thanks so much! Joyce