How To Create Cash Up Workbook
Apr 15, 2014
I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:
First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).
The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.
After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.
Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.
View 7 Replies
ADVERTISEMENT
Jan 4, 2009
i am trying to set up a spreadsheet for petty cash so will have a description and an amount on the left of screen and numerous columns on the right for the transactions to be split between ...... Is there any way for me to tell it that if A2=fuel truck then the amount in column B2 needs to also be in column G2?
View 9 Replies
View Related
Mar 6, 2007
I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
View 5 Replies
View Related
Jun 2, 2014
I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.
The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?
View 3 Replies
View Related
Jul 16, 2012
I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.
I.E.
ATL
ATL
ATL
ATL
CEN
CEN
JCK
JCK
etc.
There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV
The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.
So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.
I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.
View 5 Replies
View Related
Mar 31, 2009
I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that:
A B C
DAYTRIPCUST
I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed.
How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
View 9 Replies
View Related
Jan 20, 2014
I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.
View 2 Replies
View Related
May 13, 2014
I am building a Discounted Cash Flow.
I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.
View 1 Replies
View Related
Mar 25, 2011
The amount is $1256,
Cash Denomination will be $500, $100, $50,$10,$5 and $1. I need to split the amount into the number of pieces of each domination to present to the bank when i need to withdraw salary for the workshop workers.
500100501051
1256221011
View 5 Replies
View Related
Jan 26, 2013
I am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.
View 1 Replies
View Related
Jun 7, 2009
I'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand).
Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.
View 3 Replies
View Related
Feb 16, 2010
I have Created an Excel Spreadsheet to work as a EPOS system. The only Problem i am having finishing it off is getting the cash Drawer to open at the end of a sale.
I have my Cash Drawer connected via a Rj 45 in the Back of my Receipt Printer. The Printer i have is a Star Tsp-600. I have Found out that the Open Drawer Codes are 7 or 27,7,10,50,7. I am not entirly sure what they mean though. The code that i have at the moment doesnt work it brings an error "53" file not found. This is my code
View 3 Replies
View Related
Sep 24, 2009
i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms
criteria is named range "Criteria" (I5:K10)
So from this
Sheet1 ABCDEFGHIJKL4 Criteria 5RecTeamAmountRef RecTeamRef 6CASH NOMSAMP1132.36CAETRN CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3 *WTRN96 8CASH NOMSINTEREST915.12WTRN96 *AMPTRH 9CASH NOMSFA5-65.36AMPTRH *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG *WTWHAT 11CASH NOMSCA-9.01CBWSSG 12CASH NOMSDES-9.01MSFAST 13CASH LIFEUUTU-9.01CBWSSG 14CASH LIFEAMP1-9.01CBWSSG 15CASH LIFEAMP232.12.WSTFGH 16CASH LIFEAMP315975.36CBWSSG 17CASH LIFEAMP4-9.01OPTMOD 18CASH NOMSFA1-9.01CASH WTWHAT Excel tables to the web >> Excel Jeanie HTML 4 .........................
View 9 Replies
View Related
Nov 24, 2004
I am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
View 9 Replies
View Related
Sep 5, 2007
I have got three period like this
Period: 2010 2015 2018
Cash: 100 200 300
I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...
The three periods described above will be changeable, so the cashflows will adjust accordingly...
View 9 Replies
View Related
Aug 22, 2007
With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.
View 3 Replies
View Related
Jan 31, 2007
I've got a problem with this code, have been wracking my brains about. Here is the process I am trying to do:
1) Copy a range (a2:av1000) but (ideally) find the last populated row from a closed workbook (with a different password)
2) Create a new workbook and paste this data into it at A2
3) Close all the workbooks but only save the new one.
View 9 Replies
View Related
Jun 2, 2009
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
View 9 Replies
View Related
Nov 7, 2006
formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
View 8 Replies
View Related
May 31, 2013
How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?
View 2 Replies
View Related
May 22, 2014
I have a petty cash sheet set up:
A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)
So - this is fine - except I have to add the total amount for each department/nominal code each month.
What I'm doing at the moment is copying and pasting, sorting the sheet by:
Department
Nominal Code
Date
Then doing a manual add up of each and adding it to my month end report.
I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?
Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?
View 2 Replies
View Related
Aug 24, 2008
way to create a msg box with if you click "yes" it will copy the data from sheet 4 on workbook 1, and paste it onto a new workbook that is a copy of a standard template of workbook 1, and rename it based on a file name you give it.
ex. Pro Shop Template
Workbook 1 is Pro Shop 9/08-10/08
New Workbook 2 is a copy of Pro Shop Template but names Pro Shop 10/08-11/08
View 9 Replies
View Related
Sep 9, 2008
Is there a way of creating a new excel workbook using a macro that is stored as an Add-In.
The new workbook is to be created in the same folder. If I use the following command in macro then it saves the file in the Add-In folder.
View 14 Replies
View Related
Jul 10, 2009
I need a macro that will copy and paste the data from the range below from every file in a directory with each file in the directory on a new row. My goal is to build a database with the information in the files in the directory. I will need to be able to put the directory in the code at a later time as it has not be set up yet. it will be on the local computer and not on a network share.....
View 8 Replies
View Related
Apr 30, 2013
Is it possible to create a new workbook, add a macro to it, set it to run as an auto open event and then save and close the new workbook?
To explain a little better, I have a macro in a workbook. This workbook is opened by Filemaker which triggers the macro to run. The macro creates a number of different workbooks using information from an online file dump which is then imported into Filemaker.
However when the import is finished, I'd love for all the files that have been created to get deleted again. Now I realise I could just create a second workbook that Filemaker opens once the import is finished that deletes all the files. However I want to keep things as simple as possible for the user, and for them to have as few files as possible so things are less likely to get misplaced in the future. My concern is as they never see the workbook macro firing, they won't realise how important it is, even if I make it very clear.
So what I was thinking is at the end of the first macro, it creates another workbook, with an auto open event macro in it that simply calls a macro from the original workbook, and once created it is saved and closed. When Filemaker is finished with it's import, it could then open the new workbook which calls the macro in the original workbook. I could then have the original workbook macro delete all the added files, and close and delete the other macro enabled workbook it created. Which would leave me with the original workbook only.
View 3 Replies
View Related
Jun 11, 2014
I would like to create a macro (with button) that can be used in any workbook. I tried creating a macro in a blank workbook and added a macro button to the Quick Access toolbar. It shows up in any workbook I have open BUT when I run the macro, it also opens up the blank workbook.
View 1 Replies
View Related
Feb 17, 2010
I've created what I thought was going to be a simple macro which will create a new workbook, rename it "Applications Calls", then copy a pivot table/report from another workbook and paste it. Here's my rather amateur code which doesn't work.
Sub copyreport()
Workbooks.Add
ActiveWorkbook.Name = "Top Ten Apps Calls.xls"
Windows("Applications Calls Logged North.xlsm").Activate
Sheets("Calls Logged by Customer").Select
Cells.Select
Selection.Copy
Windows("Top Ten Apps Calls").Activate
Cells.Select
ActiveSheet.Paste
Range("A16").Select
ActiveSheet.PivotTables("PivotTable5").PivotSelect "Silo", xlButton, True
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A16").Select
End Sub
View 9 Replies
View Related
Aug 5, 2003
I want to include a help section in a workbook I created. I need something more than just a MsgBox ;-)
I'd like it to be like the windows' help that you have everywhere.
View 9 Replies
View Related
May 31, 2006
I want to the following steps to be done by vba of excel.
1. copy ranges of cells in the active worksheet (sheet3)
2. add a new workbook and paste the copied ranges to sheet1
3. save the new workbook with the new file name (e.g. date)
4. close the new workbook and clear the contents the copied ranges of cells in old workbook.
following code and make it more simpler?
Worksheets("Sheet3").Activate
If Range("l1").Value <> "" Then
Range("l1").Value = CDate(Date)
End If
asdate = Range("l1").Value 'date entered by user
resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data")
If resp4 = 7 Then
Exit Sub
View 11 Replies
View Related
Nov 28, 2007
I am fairly new in Macros, I have a workbook wich will have some data for each year month, the new month ( sheet) data will be added by a macro, copying the current sheet (month) as a base. When all months are created I need to create new workbook with only Jan (January) sheet. I created the following macro, but I think it could be reduced, in code lines, and need to how to create the new workbook based in last (December) sheet.
Sub AddSheets()
Dim wSheet As Worksheet
SheetName = ActiveSheet. Name
Select Case SheetName
Case "Jan"
On Error Resume Next
Set wSheet = Worksheets("Feb")
If wSheet Is Nothing Then
'Add Sheet Code
Sheets("Jan").Copy After:=Sheets("Jan")
ActiveSheet.Name = "Feb"
Sheets("Feb").Select...................
View 3 Replies
View Related
