Quick Question on This Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SITCFanTN - 30 Jun 2006 04:01 GMT

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

Reply to this Message

Leith Ross - 30 Jun 2006 04:38 GMT

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

Signature

Leith Ross

Reply to this Message

SITCFanTN - 30 Jun 2006 11:10 GMT

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.

> Hello SITCFanTN,
>
[quoted text clipped - 14 lines]
> Sincerely,
> Leith Ross

Reply to this Message

Dave Peterson - 30 Jun 2006 13:11 GMT

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.

> 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
[quoted text clipped - 21 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message

SITCFanTN - 01 Jul 2006 00:25 GMT

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

> I would think you'd want:
>
[quoted text clipped - 34 lines]
> >
> > End Sub

Reply to this Message