Hi All, I would be ever so grateful if you could help me out. I have set up a very simple invoice system. There is an invoice sheet (Invoice) in one work sheet and a schedule of invoice numbers (Sales Ledger) in a separate worksheet. I am trying to capture the data entered onto the invoice - into the sales ledger. In order to create a macro that will print and reset the invoice to blank each time, I must change the row in the sales ledger which matches the invoice number in cell D3 of Invoice worksheet to a value. for example invoice number 234 in Invoice worksheet will relate directly to row 234 in the sales ledger.

I have created a macro which prints the invoice, copies a row and pastes it on top of itself as a value and then deletes information input into the invoice (leaving it blank). However, the row number will change each time and I have no idea how to incorporate this into the macro.

Below is the incorrect macro:

INVOICE Macro
' Macro recorded 04/09/2009 by Lisa
'
' Keyboard Shortcut: Ctrl+p
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Sales Ledger").Select
Rows("6:6").Select
Selection.Copy
Range("A6:J6").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Invoice").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D3").Select
Selection.ClearContents
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A11:C30").Select
Selection.ClearContents
Range("D32").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*0.15"
Range("D33").Select
ActiveWindow.SmallScroll Down:=-4
End Sub

Any assistamce will be welcomed.
Thank you