PDA

View Full Version : Transfering information



angelikv
12-18-2006, 11:06 PM
Hi guys,
I have been working on the spreadsheet attached and I do not know how to transfer and record the information from one sheet to the other.
If you open the attachment you can see two tabs at the buttom(Receipt and Report).
My goal is to insert the data on the RECEIPT sheet on the cell named "Reported Sales" (having the correct date and "food stand name" which has a dropdown list)
The number that I put in that cell needs to go to the other spreadsheet called REPORT under the correct date and stand name. :think:
If any of you has an idea of how to record the data so that I just need to record the sales amount once and still have the record on the report, please let me know. :help
Thank you so much for your time and help!! :friends:

JimmyTheHand
12-19-2006, 12:39 AM
Angelica,

Welcome to VBAX! :hi:

I'd be glad to help and I think your question should be quite easy to answer, if only I could somehow extract the date from that calendar object on sheet "Receipt". But it just doesn't do anything for me. Doesn't react on clicks, cannot be moved, no popup menu on rightclick, built-in Help says nothing on it... :dunno
I've never worked with calendars before, so maybe it's just me or my Excel settings, but could you (or anyone else) tell me how this thing works?

Jimmy

Bob Phillips
12-19-2006, 02:38 AM
Dim iColumn As Long
Dim iRow As Long
Dim dte As Long

With Worksheets("Receipt")
dte = DateValue(.Range("D4").Value) - Application.Choose(Weekday(.Range("D4").Value), 0, 1, 2, 3, 4, 5, 6)
iColumn = Application.Match(dte, Worksheets("Report").Rows("7:7"), 0)
iRow = Application.Match(.Range("E6").Value, Worksheets("Report").Columns(1), 0)
Worksheets("Report").Cells(iRow, iColumn).Value = .Range("E7").Value
End With

JimmyTheHand
12-19-2006, 02:49 AM
.Range("D4").Value) :wot :doh:

(Still, my question is in the air and begs for an answer...)

Bob Phillips
12-19-2006, 04:01 AM
:wot :doh:

(Still, my question is in the air and begs for an answer...)

Look at the worksheet, you will see that the calendar date is returned in D4.

So your question was immaterial.

JimmyTheHand
12-19-2006, 04:43 AM
Look at the worksheet, you will see that the calendar date is returned in D4.

So your question was immaterial.

:dunno
I'm sorry, it wasn't so obvious for me, because all I see is the calendar frozen on 4/23/2007 whereas the date in D4 is 4/25/2007. I could've suspected a connection between the two, and could've checked it in the object's properties (controlsource or something applicable), if only the object had worked for me. But it hadn't. :(

Nevertheless, thanks for taking the time to answer me. :)

angelikv
12-19-2006, 11:26 AM
Thank you so much guys for taking time and think about my problem.
I still don't know how to tranfer the information from one side to the other based on the exact date and stand. :(
Please send me the instructions step by step, so I can solve this problem. THANK YOU!!!!!!!!!!!!!!!!!:beerchug:

Bob Phillips
12-19-2006, 12:11 PM
I showed you in post #3 in this thread.

JimmyTheHand
12-19-2006, 12:43 PM
I showed you in post #3 in this thread. Angelica,
what Bob means is (step by step)
1. Open the workbook
2. Press Alt+F11 to open Visual Basic Editor
3. In Project browser (top left window) rightclick on Sheet2 (Receipt), select "View Code"
4. Copy Bob's code inside of Private Sub CommandButton1_Click, (and you may also want to delete the green lines for they are unnecessary,) so that you get this
Private Sub CommandButton1_Click()
Dim iColumn As Long
Dim iRow As Long
Dim dte As Long

With Worksheets("Receipt")
dte = DateValue(.Range("D4").Value) - Application.Choose(Weekday(.Range("D4").Value), 0, 1, 2, 3, 4, 5, 6)
iColumn = Application.Match(dte, Worksheets("Report").Rows("7:7"), 0)
iRow = Application.Match(.Range("E6").Value, Worksheets("Report").Columns(1), 0)
Worksheets("Report").Cells(iRow, iColumn).Value = .Range("E7").Value
End With
End Sub
That's all. Hereafter, each time you click on "Print Receipt" button, the current value of Reported Sales (cell E7) gets copied to the appropriate cell on sheet "Report".

All credit :thumb goes to Bob aka xld, I was just translating :)

However, if you are not totally satisfied, please let us know. I, for example, thought that you want to keep the records for future use like this
4/25/2007 Pretzel $200
4/26/2007 Ice Cream $75
4/26/2007 Bakery $119
etc.
I also thought that you would want the value of Reported Sales added to the value already in the Report table, instead of overwriting it.

angelikv
12-19-2006, 01:30 PM
Thank you sooo much for your help!!!! :cloud9:
PERFECT!!!!!!!!!!!!!!!!!!!!! Exactly what I needed it!!!!!!!!!!!!!!!!!!!!!!

THANKS GUYS!!!!!!!!!!!!!!!!!