PDA

View Full Version : Calculate thru VBA



Prasad_Joshi
12-16-2006, 03:02 AM
Dear All,

I have a worksheet which will have 30-35 sheets. Each having formula for Opening, Closing balance, Cummulative for each day as per attached sheet. I want to keep the data of about 2-3 years in a single workbook. But as each sheet containing many formulae, the size of workbook reaches upto 4-5 MB. It's too big. I dont' want to use the formula. Whenever the data will be added in each sheet, I want to calculate each sheet by VBA & it's report should be generated in Report sheet as per the date selected by dropdown. Please help me.

Sample sheet is attached.

Or can the data will be kept in Access table ?

Thanks in advance.
Prasad

mdmackillop
12-16-2006, 04:16 AM
Is this workbook just to store data, or is its data to be changed/updated. If the former, you could Copy/PasteSpecial Values to remove all formulae.

mdmackillop
12-16-2006, 04:43 AM
Try the attached to which I've added the following WorkBook code. It should remove the need for the worksheet formulae. I've added lookup formulae to Report as the simplest way to collect the data.
Regards
MD

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Sh.Name <> "Report" Then
If Target.Row < 3 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Column
Case 3, 5, 8
Totals Target
End Select
Application.EnableEvents = True
End If
End Sub

Sub Totals(Target As Range)
Dim Rw As Long
Rw = Target.Row
If Day(Cells(Target.Row, 1)) = 1 Then
Target.Offset(, 1) = Target
Else
Target.Offset(, 1) = Target + Target.Offset(-1, 1)
End If
If Target.Row > 3 Then Cells(Rw, "B") = Cells(Rw - 1, "K")
Cells(Rw, "K") = Cells(Rw, "B") + Cells(Rw, "C") - Cells(Rw, "E") - Cells(Rw, "H")
End Sub

Prasad_Joshi
12-18-2006, 02:43 AM
Thank you so much. I will be back after using for all the sheets.
The data in the workbook is to be stored & I need to change any figure on any day.

Prasad