Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total - on a form that clears - No Accumulator!
** I HAVE ALSO POSTED THIS THREAD ON EXCEL MISCELLANEOUS ** Hi All, I've searched the threads and read through a heap, but haven't been able (so far!) to find anything to help me through what I'm trying to do. Here is a basic overview: I have a "cost calculator" for a client that allows them to entre the dimensions of an advert, select from drop down lists what paper the ad is to go into, section, colour/black & white, etc to get a cost for the advert. All of the drop down lists use Data Validation, not combo boxes. What I'm looking to do is this: Once the user has entered in the details and got the cost, I'd like them to be able to hit a "Add to List" button (I sense VB code here!) to copy some of the details from the form to another worksheet - that is, copy the name of the paper to cell A1 on Sheet2, then the cost to cell B1 on Sheet2. THEN, a reset button (if necessary), so that client can select a different paper/size, and once they have the cost for that, hit the "Add to List" button, which will then copy the same information to cell A2 and B2 respectively, on Sheet2. Does this make ANY sense to anyone? I've looked into accumulators and from what I can tell by reading and testing, they aren't going to be suitable. Any suggestions? Cheers in advance!! (Desperate) Rob. Edit/Delete Message -- Rob Moyle ------------------------------------------------------------------------ Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432 View this thread: http://www.excelforum.com/showthread...hreadid=545757 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total - on a form that clears - No Accumulator!
Here is a template for the code you could use, it copies specified cells
from sheet1 to the next available row on sheet2 and then reset the values on sheet1 Change references and ranges to copy as required. Sub CopyToList() Dim xNextRow As Long Dim wsList As Worksheet, wsData As Worksheet Set wsData = Sheets(1) Set wsList = Sheets(2) With wsList ' get location of next row on list sheet xNextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 ' copy the values form the data sheet as required .Cells(xNextRow, 1) = wsData.Range("A1") .Cells(xNextRow, 2) = wsData.Range("B1") .Cells(xNextRow, 3) = wsData.Range("C1") .Cells(xNextRow, 4) = wsData.Range("D1") End With ' reset the data form With wsData .Range("A1") = "" .Range("B1") = "" .Range("C1") = "" .Range("D1") = "" End With End Sub -- Cheers Nigel "Rob Moyle" wrote in message ... ** I HAVE ALSO POSTED THIS THREAD ON EXCEL MISCELLANEOUS ** Hi All, I've searched the threads and read through a heap, but haven't been able (so far!) to find anything to help me through what I'm trying to do. Here is a basic overview: I have a "cost calculator" for a client that allows them to entre the dimensions of an advert, select from drop down lists what paper the ad is to go into, section, colour/black & white, etc to get a cost for the advert. All of the drop down lists use Data Validation, not combo boxes. What I'm looking to do is this: Once the user has entered in the details and got the cost, I'd like them to be able to hit a "Add to List" button (I sense VB code here!) to copy some of the details from the form to another worksheet - that is, copy the name of the paper to cell A1 on Sheet2, then the cost to cell B1 on Sheet2. THEN, a reset button (if necessary), so that client can select a different paper/size, and once they have the cost for that, hit the "Add to List" button, which will then copy the same information to cell A2 and B2 respectively, on Sheet2. Does this make ANY sense to anyone? I've looked into accumulators and from what I can tell by reading and testing, they aren't going to be suitable. Any suggestions? Cheers in advance!! (Desperate) Rob. Edit/Delete Message -- Rob Moyle ------------------------------------------------------------------------ Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432 View this thread: http://www.excelforum.com/showthread...hreadid=545757 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
Running total on form | Excel Discussion (Misc queries) | |||
Running Total - on a form that clears - No Accumulator! | Excel Discussion (Misc queries) | |||
Keep a running total while using a clearable form | Excel Discussion (Misc queries) | |||
% of Running Total to Grand Total in Pivot Table | Excel Programming |