Thread: Macro to find a row (based on matching criteria) and convert that row to value

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

Hi Lisa,

Welcome to the forum. Two quick things:
1. Please use code tags (when you are editing your post, 'go advanced' select the code and click the hash (#) button). This makes it much easier for those of us used to VBA to read and decipher.
2. You have posted this in "New users" which is not a problem per se (I'd suggest you probably aren't a new user, but it doesn't really matter) - the point is that this sub-forum gets very low traffic and relatively non-specialist help, you will get much better answers if you repost this quesiton (with code tags) in the "programming" forum.

If you take my advice on this, please leave a note here to say so, so that people don't try to answer your question in two places.

CC