2 Macro questions

danielle · Mar 19th, 2002, 10:35 AM

These questions both relate to an invoice set up in a worksheet.

1. After I have finished an 'invoice' I want to transfer some data onto another worksheet called Sales Journal. I need a macro that can copy 5 cells and paste them into the Sales Journal (one at a time, not all together). But problems occur because one of the cells from the invoice is made up of a calculation so when this copies over I just get the formula rather than the figure. Then the data has to be pasted below existing entries.

2. Once the above is done I want to clear the invoice so I can enter someone else's details. Now the macro to clear the details if fine, what I want to add to it though is an automatic incrementation of the invoice number. I have no clue how to do this.

Any idea's? Would be grateful for help [grin]

davers5 · Mar 19th, 2002, 11:02 AM

Danielle--

1) If I understand your question, it sounds like your macro is pasting the formulas when you want it to paste values. I'm not sure if this was written or recorded, but if you look at the VB Editor you should see some code that looks like this:
selection.paste or activesheet.paste or something.pastespecial paste:=xlPasteAll
You want to change that to:
Selection.PasteSpecial paste:=xlValues so that you will on paste the values and not the formulas.

2)At the end of the macro that clears your invoice add Range ("the address of the cell with the invoice number").value=Range ("the address of the cell with the invoice number")+1. This will add one to the invoice number when you run the macro. If the number is in cell b2 then it looks like this:
Range("b2").value=Range("b2").value+1

I hope this helps you out.

Dave

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 10:35 AM	#1
danielle New Member Join Date: Mar 2002 Location: Melbourne, Victoria Posts: 10	These questions both relate to an invoice set up in a worksheet. 1. After I have finished an 'invoice' I want to transfer some data onto another worksheet called Sales Journal. I need a macro that can copy 5 cells and paste them into the Sales Journal (one at a time, not all together). But problems occur because one of the cells from the invoice is made up of a calculation so when this copies over I just get the formula rather than the figure. Then the data has to be pasted below existing entries. 2. Once the above is done I want to clear the invoice so I can enter someone else's details. Now the macro to clear the details if fine, what I want to add to it though is an automatic incrementation of the invoice number. I have no clue how to do this. Any idea's? Would be grateful for help [grin]

Mar 19th, 2002, 11:02 AM	#2
davers5 Board Regular Join Date: Feb 2002 Posts: 255	Danielle-- 1) If I understand your question, it sounds like your macro is pasting the formulas when you want it to paste values. I'm not sure if this was written or recorded, but if you look at the VB Editor you should see some code that looks like this: selection.paste or activesheet.paste or something.pastespecial paste:=xlPasteAll You want to change that to: Selection.PasteSpecial paste:=xlValues so that you will on paste the values and not the formulas. 2)At the end of the macro that clears your invoice add Range ("the address of the cell with the invoice number").value=Range ("the address of the cell with the invoice number")+1. This will add one to the invoice number when you run the macro. If the number is in cell b2 then it looks like this: Range("b2").value=Range("b2").value+1 I hope this helps you out. Dave