Hello all, I hope some kind soul can help! I am the treasurer of a local club and
attempting to create an Excel file to control and report upon the Club's finances.

I have two worksheets (amongst others) named 'Cash' & 'Bank' deliberately containing
identical column headers 'Date', 'Spend', 'Receive', 'For', 'To', 'From', 'Detail' &
'Balance'. On a third sheet I wish to automatically collect data from various rows that
the column 'Detail' starts with the word "Voucher number x", inserts them and then sorts
by "Voucher number x" in the 'Detail' column.

I have created two queries in MS Query for each worksheet and a macro to perform the
magic. It works, to a degree, but it only produces approximately 18 records from both
worksheets and the last one, currently extracted from the 'Cash' worksheet, does not
include a value from the 'From' column. Even after inserting dummy data, the resultant
sheet still omits any value in the column 'From'.

So, any ideas as to how I can get what I want in the third worksheet, please? Am I correct
in using MS Query or should I look elsewhere? If so, what route do I follow? All advice
gratefully received!

TIA Peter

Post a sample if you can - difficult to say what's going wrong without
it.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125534

Hello, thanks for the prompt reply!

This is the current result:

Date To Detail For Spend
06-Mar Angel - A Voucher 01 Raffles £11.77
17-Apr Rawbone - P Voucher 02 Visits £133.95
16-Apr Broad - A Voucher 03 Food Purchases £3.98
16-Apr Lewis - K Voucher 04 Food Purchases £21.51
16-Apr Willment - J Voucher 05 Food Purchases £2.00
16-Apr Willment - J Voucher 06 Raffles £20.00
16-Apr Talmer - D Voucher 07 Good Friday Walk £18.95
01-May Angel - A Voucher 08 - part Raffles £10.48
01-May Angel - A Voucher 08 - part Quizzes £2.85
01-May Angel - A Voucher 08 - part Food Purchases £4.44
01-May Denham - J Voucher 09 Food Purchases £5.00
01-May Fisher - H Voucher 10 Food Purchases £1.00
01-May Lewis - K Voucher 11 Food Purchases £10.00
13-May Willment - J Voucher 12 Food Purchases £5.00
13-May Willment - G Voucher 13 Wine, Beer & Soft Drink Purchases £18.00
05-Jun Angel - A Voucher 14 - part Food Purchases £4.38
05-Jun Angel - A Voucher 14 - part Wine, Beer & Soft Drink Purchases £11.98
05-Jun Talmer - D Voucher 15 part. Cheque 234. Food Purchases £6.38
05-Jun Talmer - D Voucher 15 part. Cheque 234. Wine, Beer & Soft Drink Purchases £6.48
05-Jun Simmons - C Voucher 16 Food Purchases £9.99
16-Jun Broad - A Voucher 17 Wine, Beer & Soft Drink Purchases £12.79
03-Jul Voucher 18 Raffles £12.63
17-Jul Willment - J Voucher 19 - G Hudson. Cheque 237 Gifts £5.50



Vouchers Total: £339.06

The problem is that Voucher 18 does not display any info in the 'To' Column or for another
subsequent data in the two worksheets - that's part of the problem!


-------- Original Message --------

Post a sample if you can - difficult to say what's going wrong without
it.

Well, I was really after a bit of macro code and perhaps a query or two
- say, in a workbook? If it's allowed to attach here (codecage.com).

"I have created two queries in MS Query for each worksheet and a macro
to perform the magic. It works, to a degree"


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125534

Peter;453475 Wrote:
Hello all, I hope some kind soul can help! I am the treasurer of a local
club and
attempting to create an Excel file to control and report upon the
Club's finances.

I have two worksheets (amongst others) named 'Cash' & 'Bank'
deliberately containing
identical column headers 'Date', 'Spend', 'Receive', 'For', 'To',
'From', 'Detail' &
'Balance'. On a third sheet I wish to automatically collect data from
various rows that
the column 'Detail' starts with the word "Voucher number x", inserts
them and then sorts
by "Voucher number x" in the 'Detail' column.

I have created two queries in MS Query for each worksheet and a macro
to perform the
magic. It works, to a degree, but it only produces approximately 18
records from both
worksheets and the last one, currently extracted from the 'Cash'
worksheet, does not
include a value from the 'From' column. Even after inserting dummy
data, the resultant
sheet still omits any value in the column 'From'.

So, any ideas as to how I can get what I want in the third worksheet,
please? Am I correct
in using MS Query or should I look elsewhere? If so, what route do I
follow? All advice
gratefully received!

TIA Peter

p45cal;453777 Wrote:
Well, I was really after a bit of macro code and perhaps a query or two
- say, in a workbook? If it's allowed to attach here (codecage.com).

Good morning,

Having now subscribed to Code Cage I hope that the email I am
attempting to attach the file in question :-)


+-------------------------------------------------------------------+
|Filename: Accounting Test.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=218|
+-------------------------------------------------------------------+

--
Peter
------------------------------------------------------------------------
Peter's Profile: http://www.thecodecage.com/forumz/member.php?userid=687
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125534

I haven't looked at your workbook in detail or your code at all,
however, if all you want to do is copy any row that has the word voucher
in column G then this would suffice:

Code:
--------------------
Sub Copy_Vouchers()
Dim MyCell As Range
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Cash", "Bank")) 'add sheets as needed
For Each MyCell In Sheets(Sh.Name).Range("G1:G" & Sheets(Sh.Name).Range("G" & Rows.Count).End(xlUp).Row)
If Left(MyCell.Text, 7) = "Voucher" Then
MyCell.EntireRow.Copy Destination:=Sheets("Vouchers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
Next Sh
End Sub

--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125534