totaling at end 1

[msnook]

I am using the following coding to copy data from one sheet and place it on a differents sheet stating with row 7 and then going down qith each successive row of data. At the end of this I need to total up a column. In a normal sheet I would put =sum(D714) but the problem is that there is no set length the number of entries will be. Sometimes it might be 1 and other times 10. Is there a way to do this in my coding. The way i did it is the second example I included.


y = 15
s = 11
z = (Worksheets("Part B").Range("A11"))

For a = 7 To 30

With Worksheets("Data")

If ((.Range("D" & a)) = z) Then
.Range("E" & a).Copy
Sheets("Part B").Select
Range("B" & y).Select
ActiveSheet.Paste
.Range("H" & a).Copy
Sheets("Part B").Select
Range("C" & y).Select
ActiveSheet.Paste
.Range("N" & a).Copy
Sheets("Part B").Select
Range("D" & y).Select
ActiveSheet.Paste
.Range("L" & a).Copy
Sheets("Part B").Select
Range("E" & y).Select
ActiveSheet.Paste
.Range("F" & a).Copy
Sheets("Part B").Select
Range("F" & y).Select
ActiveSheet.Paste
y = y + 1
End If

End With
Next a

-----------------------------------------------------
If y > 15 Then
Range("A" & y + 1).Select
Selection.AutoFill Destination:=Range("A" & y + 1, "J" & y + 1), Type:=xlFillDefault


A & y+1 is entered as =Sum(A6:A10)

 

[SkipVought]

Hi,

Aggregations at the BOTTOM of a column is a vestage of the old paper, pencil and adding machine era. You'ld write the subtotal at the bottom of each page and finally, punch in all the subtotals to get a grand total.

But that's all changed with electronic spreadsheet technology. You do not have to put the totals in a place where the user has to hunt, maybe scroll and scroll to get the summary. It can be at the TOP. Always in the same place. No guessing!.

I put my totals down in the front row! Numero uno!

But if you insist on totals at the bottom, take a look at the Data > Subtotal wizard. It will take you less than 10 seconds to implement.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[msnook]

Skip,
I do not know is you will see this but that worked great. Sometimes I get so locked into one idea it is hard to change the way of thinking. Putting the total at the top of the page worked our great.