Multiple question requiring asistance please

[NoodNutt]

G'day everyone

Everyday I generate multiple reports, of which the information is extracted
from a Citrix DB, I have automated most of the reports using 2003 and am
onto the final stage.

We have 15 or so Major clients that I manually put into a Linehaul Report
daily

The code below works a treat on most of what I require, but I now need to go
a few steps further to satisfy my criteria for the Sheet layout.

Sub Split_Data()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Start of NSW

Sheets("NSW").Select

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A5")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

Range("A4:O50").Select
Selection.Sort Key1:=Range("A4:A50"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

'End of NSW

step 1.

I need to change:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

to

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=FEDEX"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A8")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

which then brings us to step 2.

Step 2.

Field/Column 10 generally has multiple words in it eg "FEDEX / DEPOT/ Blah,
blah, etc....."
I need to use a wildcard like "=LIKE("FEDEX"*") to filter out only those
rows that contain just that.

Then go to the Linehaul Sheet, paste in the Name.Value(FEDEX) into A8
Then Paste.Value all the matching rows into A9 until there are no more.
Then Drop down 2 rows and set the focus on that cell.

Step 3.

Go back to the search again:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=POST LOG"

Then go to the Linehaul Sheet, paste in the Name.Value(POST LOG) into the
last cell focus

And so on until all my 15 majors have been accounted for, and in saying
that, I probably need to include a comment like "No Movements" in the event
no match is found.

Which would still require the Name.Value pasted in the next available cell,
then in the next cell below that have it display the comment.

It's a big ask I know, so I won't be overly disappointed should this be
somewhat "pie in the sky"

Thx heaps in advance & I am sincerely looking forward to any
comments/suggestion anyone has to offer.

Regards
Mark.

 

[smartin]

G'day everyone

Everyday I generate multiple reports, of which the information is extracted
from a Citrix DB, I have automated most of the reports using 2003 and am
onto the final stage.

We have 15 or so Major clients that I manually put into a Linehaul Report
daily

The code below works a treat on most of what I require, but I now need to go
a few steps further to satisfy my criteria for the Sheet layout.

Sub Split_Data()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Start of NSW

Sheets("NSW").Select

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A5")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

Range("A4:O50").Select
Selection.Sort Key1:=Range("A4:A50"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

'End of NSW

step 1.

I need to change:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

to

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=FEDEX"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A8")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

which then brings us to step 2.

Step 2.

Field/Column 10 generally has multiple words in it eg "FEDEX / DEPOT/ Blah,
blah, etc....."
I need to use a wildcard like "=LIKE("FEDEX"*") to filter out only those
rows that contain just that.

Then go to the Linehaul Sheet, paste in the Name.Value(FEDEX) into A8
Then Paste.Value all the matching rows into A9 until there are no more.
Then Drop down 2 rows and set the focus on that cell.

Step 3.

Go back to the search again:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=POST LOG"

Then go to the Linehaul Sheet, paste in the Name.Value(POST LOG) into the
last cell focus

And so on until all my 15 majors have been accounted for, and in saying
that, I probably need to include a comment like "No Movements" in the event
no match is found.

Which would still require the Name.Value pasted in the next available cell,
then in the next cell below that have it display the comment.

It's a big ask I know, so I won't be overly disappointed should this be
somewhat "pie in the sky"

Thx heaps in advance & I am sincerely looking forward to any
comments/suggestion anyone has to offer.

Regards
Mark.

G'day Mark,

Seems you have good ideas and a good grasp of the Excel Object Model.
What was the question? Are you stuck somewhere?

 

[NoodNutt]

G'day

Everday is a learning day, never too old to learn something new!

As far as the Question:

I need help expanding my existing code to incorporate the steps I have
outlined in my previous thread.

Regards
Mark.

 

[JLatham]

As smartin noted, you seem to have a very good grasp of the object model and
of the definition of your requirements. I don't see why the additional code
you've shown would not work if you simply brute force things and make it a
very long straight-line section to get all of the work done.

Another approach would be to write the sections where you are doing the same
thing, just with different columns for filtering and different destination
sheets as either Subs or Functions that can be called while passing the
different column, filter value and sheet names to them.

Example - you write the following Sub

Sub SetFilters(sourceSheetName as string, destSheetName as string,
filterCrit as string)
Dim sourceSheet as Worksheet
Dim destSheet as Worksheet
Dim rng As Range
Dim criteriaStatement As String

Set sourceSheet = Worksheets(sourceSheetName)
Set rng = sourceSheet.Range("A8:O" & Rows.Count)
Set destSheet = Worksheets(destSheetName)
criteriaStatement = "=" & filterCrit ' add "=" to start of passed criteria

sourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:=criteriaStatement

'cleanup: return resources to the system
Set rng = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub

That's just an example - from the main routine you would call it using a
code statement such as this one:
SetFilters "Data", "NSW", "SYD"

One or two hopefully helpful hints:

To find the last used row in a column (as when setting up a sort) can be
found by a function similar to this (and I'll use full reference, which could
be shortened in some cases) :
Dim lastUsedRow As Long
lastUsedRow = _
Worksheets("sheetname").Range("A" & Rows.Count).End(xlUp).Row
where "A" is the column to find last used row in.

Think of using it this way to get a "minimal" range to sort:
Range("A4:O" & _
Worksheets("sheetname").Range("A" & Rows.Count).End(xlUp).Row).Select
Selection.Sort ....

Also, I don't think you need to reference the entire range of a column used
in a sort as the SortKey#. Where you have
Selection.Sort Key1:=Range("A4:A50"), ...
I believe that
Selection.Sort Key1:=Range("A4"), ...
is sufficient.

Final, more advanced, hint. Normally I wouldn't offer this, but you seem to
have a grasp of using objects created with the Set statement and by using
them you can both increase the efficiency of your code AND reduce jumping
around to various sheets to get work done (which reduces screen flicker and
user annoyance).

It appears that you're selecting your destination sheet because of the
apparent need to select the range to be sorted later on. This was something
I did for a long time myself because I couldn't seem to find a way around the
need to have the range selected for the .Sort method to work. But you can do
a sort on a sheet without having to first select either the sheet or the
range to be sorted! Here is some sample code that would do that - I've kept
it basic so you can hopefully see just what's happening - in this case
sorting the same range, but on different sheets:

Sub SortAnySheet(sheetName As String)
Dim sortSheet as Worksheet
Dim sortRange as Range
Dim sortKey as Range

Set sortSheet = Worksheets(sheetName)
set sortRange = sortSheet.Range("A4:O" & _
sortSheet.Range("A" & Rows.Count).End(xlUp).Row)
set sortKey = sortSheet.Range("A4")

sortRange.Sort key1:=sortKey, Order1:=xlAscending, Header:=xlyes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'cleanup
Set sortKey = Nothing
Set sortRange = Nothing
Set sortSheet = Nothing
End Sub

NOTE for Pre-Excel 2003 users: do not include the DataOption# portion of the
..Sort function, not used in some earlier versions, and works just fine even
in 2003 without it/them. Even if you're using 2003 but there is the chance
that the workbook will be used by someone with an earlier version of Excel,
leave it off to keep a run time error from occuring on their systems.

 

[NoodNutt]

Thank you to both

Wasn't 100% certain if I could incorporate it all into 1, now that you both
have given me a nudge in the right direction, I will have a crack at it.

Thx again
Regards
Mark.