Excel (XP) - longish, sorry

[Peter]

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]

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

 

[Peter]

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

 

[p45cal]

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

 

[Peter]

Hello all, I hope some kind soul can help! I am the treasurer of a loca
club and
attempting to create an Excel file to control and report upon th
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 fro
various rows that
the column 'Detail' starts with the word "Voucher number x", insert
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 macr
to perform the
magic. It works, to a degree, but it only produces approximately 1
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 dumm
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
follow? All advice
gratefully received!

TIA Peter

Well, I was really after a bit of macro code and perhaps a query or tw
- 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 a
attempting to attach the file in question :-

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

 

[Simon Lloyd]

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 vouche
in column G then this would suffice

Code
-------------------
Sub Copy_Vouchers(
Dim MyCell As Rang
Dim Sh As Workshee
For Each Sh In Sheets(Array("Cash", "Bank")) 'add sheets as neede
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" The
MyCell.EntireRow.Copy Destination:=Sheets("Vouchers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0
End I
Next MyCel
Next S
End Su

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

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Simon Lloyd]

Title of thread changed.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[p45cal]

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

 

[Peter]

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 vouche
in column G then this would suffice: Code
-------------------
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 workshee
"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 a
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

Pete

 

[p45cal]

I suspect this line will never be true:
If Left(MyCell.Text, 4) = "Annual subscriptions"
Try:
If Left(MyCell.Text, *20*) = "Annual subscriptions"

 

[Simon Lloyd]

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 row
in column D, that is to say if you have 25 enties in coulmn D your rang
would be G1:G25 and not the amount of rows in coulmn G. but no matte
what that statement says that you ARE processing column G

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Peter]

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]

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

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

Simon

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

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

I've now developed the workbook to run additional macro's & procedure
to create further 'reports' (I now have three, all of which produce wha
I want) and it has crossed my mind that there may be a VBA method tha
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 gratefull
received

Enjoy the weeken

Regard

Pete

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com