Excel: Need formula to forward to from one worksheet to another 1

[russell123]

Specifically, I have a few columns in one worksheet that include quantity, description, price plus some other columns. But these cells are the most important.

The next worksheet is a standard invoice sheet. I would like to automate that if a quantity appears in the previous worksheet that these three cells above are copied to specific cells in current invoice worksheet, thereby 'not' copying rows where there is no quantity to not unneccessarily populating the invoice sheet.

can this be done in excel?

with great respect for programmers,

Russell

 

[russell123]

MisterC,

Thanks for your generosity and your humour.

I ajusted point #2. you were right of course. It was reading the totals from the sourse sheet.

No doubt you know that I am a novice at this, so saying that,

1. How do I 'call' the code from the Invoice sheet's Activate event so that it runs automatically when there is input on source sheet?

2. I would like to protect the sheet so no one inadvertantly deletes or changes the formulas, text, etc. when I lock then protect the worksheet (selected locked cells, and selected unlocked cells), the macro won't run. Any ideas?

Truely grateful,

Russell

 

[MisterC]

You've created a module with the subroutine CreateInvoice in it. All you need to do is open the Invoice sheet's VBA editor and enter CreateInvoice in the Activate event:

Private Sub Worksheet_Activate()
    CreateInvoice
End Sub

Now, it doesn't run when there is input on the source sheet; It runs when the Invoice sheet is "Activated" (when you click on the Invoice tab).

No problem with the sheet protection, just have your code "unprotect" it at the start and "protect" it back at the finish.

Worksheets("Invoice").UnProtect

and

Worksheets("Invoice").Protect

 

[russell123]

MisterC,

Thanks for the protect/unprotect. It works fine.

1. I'm not sure where to exactly put,

Private Sub Worksheet_Activate()
CreateInvoice
End Sub

I've tried it in a few areas under and over the active events (what i think are activate events). It doesn't seem to work. Perhaps using the code above you can tell me where to place these statements.

thank you once again.

Russell

 

[MisterC]

In the VBA environment, look at your project explorer. Thats the window that shows a treeview of your workbook. It should look something like:

-|VBAProject (Workbook Name)
 |---|- Microsoft Excel Objects
 |   |----Sheet1 (Sheet Name)
 |   |----Sheet2 (Sheet Name)
 |   |----Sheet3 (Sheet Name)
 |   L----ThisWorkbook
 |
 L---|-Modules
     L----Module1

Double Click on the Invoice Sheet in the project explorer to open it's code window and paste the code there. Now you have assigned code to the Invoice Sheet's Activate event and it will run whenever you select that sheet tab in the workbook.

 

[russell123]

MisterC,

I put it in the section you suggested and it did indeed copy the input over to the invoice sheet when I clicked on the Invoice sheet, however it seemed to go into an endless loop back and forth from sheet1(order) to sheet2(invoice). I had to end excel and restart it.

Any ideas?

 

[MisterC]

Yep. The easiest solution would be to send me the spreadsheet and I'll fix it and return it. Just send it to tek@thmco.com.