Running Total - on a form that clears - No Accumulator!

[Rob Moyle]

** 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

[Nigel]

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" <[email protected]> wrote
in message news:[email protected]...
>
> ** 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
>