Worksheet for Consolidating various Party Account

[Rashid Khan]

Hello All Experts,
I am using Office XP / Windows XP
I have a workbook with many Sheets1 (around 40+).
The format as shown below is similar on all the 40+ sheets. There are
separate sheets for each party.

A1 - PartyCode
B1 - PartyName
C1 - BillNo.
D1 - BillDate
E1 - BillAmount
F1 - AmountPaid
G1 - PaymentDate
H1 - PaidBy (Cheque, Cash etc)
I1 - Balance
The party usually does not pay on Bill to Bill basis. It pays a lump sum
amount eg if the BillAmount is 100,000 the party pays say 80,000 (the
balance 20,000 is carried forward) and is paid in full or in part depending
on the amount of the next Bill. This is causing my confusion. If the bill
was cleared on Bill to Bill basis I would have set up a simple formula in I1
Balance = BillAmount - AmountPaid.

How can I go about setting a MasterSheet where as I can get the details of
Billed Amount, AmountPaid, Balance etc for each individual party? Will I
have to use linking all the individual party sheets to the MasterSheet or
any VBA automation is required????

Any help or better suggestions would be greatly appreciated

TIA

Rashid

 

[Dave Peterson]

I wouldn't use linking--too many things can go wrong.

But how about if you combined all the worksheets onto one giant worksheet?

If yes, then I assumed that the real data started in row 2 (headers in row 1).

Option Explicit
Sub testme()
Dim newWks As Worksheet
Dim wks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range

Set newWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))

newWks.Name = "Cons " & Format(Now, "yyyymmdd_hhmmss")

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = newWks.Name Then
'do nothing
Else
With wks
Set RngToCopy = .Range("a2:i" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell
End If
Next wks

'get headers from first worksheet
Worksheets(1).Rows(1).Copy _
Destination:=newWks.Range("a1")

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Rashid Khan]

Thanks Dave,
Your suggestion works. But I have put the TotalBilled, TotalBalance,
TotalDue in all individual Sheets in Row 55, 56 and 57. If your macro can
copy only these three details one below other to the Master Sheet in the
following format then I think I will achieve what I want.

Col A - PartyName (PartyName is equal to the Sheet Name)
Col B - TotalBilled
Col C - TotalBalance
Col D - TotalDue

This way I would have individual Sheets for each party and the consolidated
MasterSheet only displaying the TotalBilled, TotalBalance and TotalDue.
Hope I am clear to you.
Thanks
Rashid

 

[Dave Peterson]

I'm gonna guess that those totals are in column A. If not, you can
change the addresses in the code:

Option Explicit
Option Base 0
Sub testme2()
Dim newWks As Worksheet
Dim wks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range
Dim iCtr As Long
Dim myAddresses As Variant
Dim oRow As Long
'billed, balance, due
myAddresses = Array("a55", "a56", "a57")

Set newWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))

With newWks
.Name = "Cons " & Format(Now, "yyyymmdd_hhmmss")
.Range("a1").Resize(1, 4).Value _
= Array("PartyName", "TotalBilled", "TotalBalance",
"TotalDue")
oRow = 1
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = newWks.Name Then
'do nothing
Else
oRow = oRow + 1
With wks
newWks.Cells(oRow, "A").Value = .Name
For iCtr = LBound(myAddresses) To UBound(myAddresses)
newWks.Cells(oRow, "A").Offset(0, 1 + iCtr).Value
_
= .Range(myAddresses(iCtr)).Value
Next iCtr
End With
End If
Next wks

End Sub



This is the line to adjust:
myAddresses = Array("a55", "a56", "a57")

Watch out for line wrap--I posted through google.

 

[Rashid Khan]

Hi Dave,
It works like superman.
Thanks a million. You r great.
Rashid