Pls try with this simple sub.

Sub MACRO()
Sheets("Sheet1").Select
Rows("4:4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("a2").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Range("e5").Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If ActiveCell.Value < "" Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
ActiveCell.Offset(0, 4).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Sheet2").Select
a = ActiveCell.Row
b = Application.WorksheetFunction.SumProduct(Range(Cel ls(a, 4),
Cells(3, 4)), Range(Cells(a, 5), Cells(3, 5)))
ActiveCell.Offset(0, 3).Value = "Total: $" & b
c = Application.WorksheetFunction.Sum(Range(Cells(a, 5), Cells(3, 5)))
ActiveCell.Offset(0, 4).Value = c & " of items"
Columns("C:C").Delete Shift:=xlToLeft
End Sub

Laura90210 wrote in message ...
Tom, thanks for your reply.

I tried that and I got a debugger.... :(

It sounds very complicated and brilliant though.....


I have attached my price list to this. If you had 5 minutes to spare
and could have a quick look for me that would really make my day.

Laura x


+----------------------------------------------------------------+
| Attachment filename: watkins & powis price list.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=355302|
+----------------------------------------------------------------+

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/