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

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