Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row at start of each subtotal
Hi I'm a newbie at VBA, would really appreciate if someone can help me
with the following problem:- I have a data table with columns:- Date Invoice Number Amount Customer Address1 Address2 Address3 I sort the table by Customer, then by Date then I subtotal the Amount at each Customer change, with pagebreak What I want is a Statement for each Customer:- If at each page (each start of subtotal); I can copy the Name, Address1, Address2, Address 3 range and insert in . This is then repeated for all subtotal (pagebreak). At the end, Customer, Add1, Add2, Add3 columns are deleted. -------------------------------------------------------------------------------------------------------------------------------- Below is a code I write to insert some values in certain columns on the subtotal column But I don't think it can work at all for this Statement I wish to create... Sub ChequePayment() Dim Counter As Integer Dim iCounter As Integer Dim iValue As Double Dim txtBox As String Sheets("PrintOut").Range("B5").Select txtBox = InputBox("Please enter Cheque No.:") iValue = txtBox - 1 For iCounter = 1 To 200 iValue = iValue + 1 Do Until IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select 'MOVE DOWN A CELL Loop ActiveCell.Offset(0, 1).Select 'MOVE RIGHT AND CHECK IF THIS ONE IS EMPTY If IsEmpty(ActiveCell) Then Exit Sub Else ActiveCell.Offset(0, -1).Select 'Move Back ActiveCell.Offset(0, 3).Select 'Move to Cell E ActiveCell.FormulaR1C1 = iValue ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "'1311" ActiveCell.Offset(0, -4).Select ActiveCell.Offset(1, 0).Select 'MOVE DOWN A CELL End If Next iCounter End Sub ------------------------------------------------------------------------------------------------------------------------------------------- Thanks, Ame |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Rows vs SUM() and SUBTOTAL() | Excel Discussion (Misc queries) | |||
How do I insert a subtotal? | Excel Discussion (Misc queries) | |||
Insert Subtotal button | Excel Worksheet Functions | |||
Insert a new worksheet after subtotal | Excel Worksheet Functions | |||
Sort, Subtotal, Label Subtotal, Insert row | Excel Programming |