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

Title of thread changed.


--
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

-but it only produces approximately 18 records from both worksheets-

After duplicating the file (as you nust do each time to get up-to-date
results) and making minor adjustments for directory structure to your
code, I got 23 records in total from both sheets. I haven't tried this,
but are there any completely blank rows within the data tables in either
of the Cash or Bank sheets? 23 results appears to be what should be
expected.

-does not include a value from the 'From' column.-

That's because the -From -column isn't included in either of the select
queries;
change the -CommandText -line to:
.CommandText = Array("SELECT `Cash$`.Date, `Cash$`.To,
`Cash$`.From,`Cash$`.Detail, `Cash$`.For, `Cash$`.Spend" & Chr(13) & ""
& Chr(10) & "FROM `Cash$` `Cash$`" & Chr(13) & "" & Chr(10) & "WHERE
(`Cash$`.Detail Like 'Voucher%')" & Chr(13) & "" & Chr(10) & "ORDER BY
`Cash$`.Detail")
and
.CommandText = Array("SELECT `Bank$`.Date, `Bank$`.To,
`Bank$`.From,`Bank$`.Detail, `Bank$`.For, `Bank$`.Spend" & Chr(13) & ""
& Chr(10) & "FROM `Bank$` `Bank$`" & Chr(13) & "" & Chr(10) & "WHERE
(`Bank$`.Detail Like 'Voucher%')" & Chr(13) & "" & Chr(10) & "ORDER BY
`Bank$`.Detail")and you'll have to change any reference to
column E in the code, to column F.

It's a good idea to delete redundant querytables (you had 10 in your
Voucher sheet) with code such as:Do Until
ActiveSheet.QueryTables.Count = 0
ActiveSheet.QueryTables(1).Delete
Loopearly on in your procedure.

Since it seems you are extracting data from effectively the same
workbook I think the query approach to be a bit clumsy, and that it'd be
easier to take Simon's approach, especially as you're consolidating data
from two sheets into one table.


--
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

Simon Lloyd;454461 Wrote:
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

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


Hello Simon

Your solution worked, thank you. I have now added a new worksheet
"Subs" and added the following code (based on yours):

Sub Subs()
Dim MyCell As Range
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Cash", "Bank", "Stock")) 'add sheets as
needed
For Each MyCell In Sheets(Sh.Name).Range("G1:G" &
Sheets(Sh.Name).Range("D" & Rows.Count).End(xlUp).Row)
If Left(MyCell.Text, 4) = "Annual subscriptions" Then
MyCell.EntireRow.Copy Destination:=Sheets("Subs").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
Next Sh
End Sub

It doesn't work! Any ideas?!

From a very VBA beginner

Regards

Peter


--
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 suspect this line will never be true:
If Left(MyCell.Text, 4) = "Annual subscriptions"
Try:
If Left(MyCell.Text, *20*) = "Annual subscriptions"

Peter;457205 Wrote:
Hello Simon

Your solution worked, thank you. I have now added a new worksheet
"Subs" and added the following code (based on yours):

Sub Subs()
Dim MyCell As Range
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Cash", "Bank", "Stock")) 'add sheets as
needed
For Each MyCell In Sheets(Sh.Name).Range("G1:G" &
Sheets(Sh.Name).Range("D" & Rows.Count).End(xlUp).Row)
If Left(MyCell.Text, 4) = "Annual subscriptions" Then
MyCell.EntireRow.Copy Destination:=Sheets("Subs").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
Next Sh
End Sub

It doesn't work! Any ideas?!

From a very VBA beginner

Regards

Peter


--
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

You also need to look at this line:
Code:
--------------------
For Each MyCell In Sheets(Sh.Name).Range("G1:G" & Sheets(Sh.Name).Range("D" & Rows.Count).End(xlUp).Row)

--------------------
this will give you a workable range of G1:G and the number of used rows
in column D, that is to say if you have 25 enties in coulmn D your range
would be G1:G25 and not the amount of rows in coulmn G. but no matter
what that statement says that you ARE processing column G!


--
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

Simon,

Very many thanks - and to P45cal too - for your expertise.

I'm beginning to understand and it works! Thank you:Bgr

I've now developed the workbook to run additional macro's & procedures
to create further 'reports' (I now have three, all of which produce what
I want) and it has crossed my mind that there may be a VBA method that
when run, it requests which report is required i.e. 'Vouchers',
'Subscriptions' etc.

Do you know if this is possible? If so, any advice/code gratefully
received!

Enjoy the weekend

Regards

Peter

Simon Lloyd;459831 Wrote:
You also need to look at this line:
Code:
--------------------
For Each MyCell In Sheets(Sh.Name).Range("G1:G" & Sheets(Sh.Name).Range("D" & Rows.Count).End(xlUp).Row)

--------------------
this will give you a workable range of G1:G and the number of used
rows in column D, that is to say if you have 25 enties in coulmn D
your range would be G1:G25 and not the amount of rows in coulmn G. but
no matter what that statement says that you ARE processing column G!


--
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

A simple IF statement and inputbox should do, incorporate this in to
your code or trigger method

Code:
--------------------
Dim IB As String
IB = Application.InputBox("Please Enter Your Desired Report" & vbLf & "Vouchers" & vbLf & "Subscriptions" _
& vbLf & "Whatever", "Report Selection", "Vouchers")
If LCase(IB) = "vouchers" Then
MsgBox "This would have run Vouchers Report" 'your code goes here like Call Vouchers
ElseIf LCase(IB) = "subscriptions" Then
MsgBox "This would have run Subscriptions Report" 'your code goes here like Call Subscriptions
ElseIf LCase(IB) = "whatever" Then
MsgBox "This would have run Whatever else!" 'your code goes here like Call Your module name
End If
--------------------


Peter;461601 Wrote:
Simon,

Very many thanks - and to P45cal too - for your expertise.

I'm beginning to understand and it works! Thank you:Bgr

I've now developed the workbook to run additional macro's & procedures
to create further 'reports' (I now have three, all of which produce what
I want) and it has crossed my mind that there may be a VBA method that
when run, it requests which report is required i.e. 'Vouchers',
'Subscriptions' etc.

Do you know if this is possible? If so, any advice/code gratefully
received!

Enjoy the weekend

Regards

Peter


--
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