Petty Cash book

[Akash]

Hi,

I tried to create a Petty Cash Book in Excel. The format is this
________________________________________________________________________
A B C
D E F
________________________________________________________________________

From 01/01/2006 to 03/07/2006

________________________________________________________________________
Balance 388.82
________________________________________________________________________
Date Receipt No. Description Received By Deposit
Withdrawn
________________________________________________________________________
01/01/2006 1011 Deposit to petty cash 500.00
03/01/2006 243 Lunch for overtime workers Akash
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/07/2006 243 Lunch for overtime workers
18.53
________________________________________________________________________

Totals Rs.
500. Rs. 111.18



I have a Coulm Date starting from A6:A50

In the Cell Number A3 i want the Out Put as" From Min Date to maximum
Date "

So I used the below mentioned formulae to get the desired result.

"From "&TEXT(MIN(A6:A50),"dd/mm/yyyy")&" to
"&TEXT(MAX(A6:A50),"dd/mm/yyyy")

I want a macro through which I dont have to put the the above mentioned
formulae in A3 Cell.

More over i want that as soon as the user feels that he has enter
sufficient amount of details in Sheet1 and presses a shortcut key then
a new sheet with the name of current date should activate with with the
below mentioned format.
________________________________________________________________________
A B C
D E F
________________________________________________________________________

From 01/01/2006 to 01/01/2006

________________________________________________________________________
Balance 388.82
________________________________________________________________________
Date Receipt No. Description Received By Deposit
Withdrawn
________________________________________________________________________
01/01/2006 1011 Deposit to petty cash 388.82

________________________________________________________________________

Totals Rs.
388.82. Rs.


Is this possible.

Please Do help me in this regards

Akash

 

[Akash]

Hi,

I tried to create a Petty Cash Book in Excel. The format is this
________________________________________________________________________
A B C
D E F
________________________________________________________________________>From 01/01/2006 to 03/07/2006________________________________________________________________________
Balance 388.82
________________________________________________________________________
Date Receipt No. Description Received By Deposit
Withdrawn
________________________________________________________________________
01/01/2006 1011 Deposit to petty cash 500.00
03/01/2006 243 Lunch for overtime workers Akash
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/01/2006 243 Lunch for overtime workers
18.53
03/07/2006 243 Lunch for overtime workers
18.53
________________________________________________________________________

Totals Rs.
500. Rs. 111.18

I have a Coulm Date starting from A6:A50

In the Cell Number A3 i want the Out Put as" From Min Date to maximum
Date "

So I used the below mentioned formulae to get the desired result.

"From "&TEXT(MIN(A6:A50),"dd/mm/yyyy")&" to
"&TEXT(MAX(A6:A50),"dd/mm/yyyy")

I want a macro through which I dont have to put the the above mentioned
formulae in A3 Cell.

More over i want that as soon as the user feels that he has enter
sufficient amount of details in Sheet1 and presses a shortcut key then
a new sheet with the name of current date should activate with with the
below mentioned format.
________________________________________________________________________
A B C
D E F
________________________________________________________________________>From 01/01/2006 to 01/01/2006________________________________________________________________________
Balance 388.82
________________________________________________________________________
Date Receipt No. Description Received By Deposit
Withdrawn
________________________________________________________________________
01/01/2006 1011 Deposit to petty cash 388.82

________________________________________________________________________

Totals Rs.
388.82. Rs.

Is this possible.

Please Do help me in this regards

Akash

________________________________________________________________________
________________________________________________________________________
________________________________________________________________________



Sub New_Sheet_Same_Format()
'
' New_Sheet_Same_Format Macro
' Macro recorded 23/12/2006 by Akash Maheshwari
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Move After:=Sheets(2)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "23rd Dec"
Range("E14").Select
Sheets("Petty Cash Log").Select
Cells.Select
Range("D8").Activate
Selection.Copy
Sheets("23rd Dec").Select
Cells.Select
Range("E14").Activate
ActiveSheet.Paste
Range("A6:G7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F10").Select
ActiveWindow.SmallScroll Down:=-12
Range("A6").Select
ActiveCell.FormulaR1C1 = "12/23/2006"
Range("C6").Select
ActiveCell.FormulaR1C1 = "='Petty Cash Log'!R[-2]C[-2]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Petty Cash Log'!R[-2]C[-3]"
Range("E7").Select
End Sub


I tried this code. Its working only once. i.e. Once i press the
shortcut key its creating a sheet, Renaming it to current Date &
copying the entire sheet1 to sheet2, blanking it and then puttting the
current date & then transfering the remaining balance to the Sheet2.

But if the shortcut key is pressed 2nd time it showing me "Runtime
Error No: 9" Subscript out of Range.

Pls do help me in this regards

Where i m lacking