R
rob.allchurch
I used to think I was OK in VBA, but now I'm not so sure ...
I have a sheet within which I have a list linked to a MOSS list.
(The file was created in XL2003 and I'm using XL2007, so the
synchronization functionality is working just fine)
I have another sheet on which I wish to filter selected records from
the MOSS-linked list.
The following code is called from the 'receiving' sheet:
Sub Advanced_Filter_Due_To_Complete_Projects()
Sheets("Projects Due For Completion").Activate
Range("H2").Value = ">=" &
Format(Range("To_Complete_Date_1").Value,"dd/mm/yyyy")
Range("I2").Value = "<=" &
Format(Range("To_Complete_Date_2").Value,"dd/mm/yyyy")
ActiveSheet.Range(Range("B5").End(xlDown),
Range("B5").End(xlToRight).Offset(1, 0)).ClearContents
Range("'MOSS Projects In Progress'!
Projects_In_Progress").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("H1:I2"),
CopyToRange:=Range("B5:E5"), Unique:=False
End Sub
The 'destination' sheet is 'Projects Due For Completion'
The 'source' sheet is 'MOSS Projects In Progress'
The MOSS-linked list is named 'Projects_In_Progress'.
If I record a macro whilst performing the filter, obviously everything
works just fine. Running the code afterwards though produces no result
at all.
I've tried qualifying the ranges in a variety of different ways
(sheets(...).range(...), range(...) etc.), but no joy.
I've also changed the criteria from being calculated as a formula to
being created as a string value and entered (as in the code above). I
read on another thread that it may be something to do with string
manipulation of date fields by XL? (the format on the source sheet
list is 'dd/mm/yyyy' as in the code above)
I can only guess as to why its not working - maybe something to do
with the list/'name' in XL2007? I have tried creating a dynamic range
name for the list "MOSS_Projects_In_Progress" referring to the list
just in case that's it, but that doesn't work either.
Basically, I'm stumped and can't see the solution. No doubt its
something obvious and I'll kick myself once its pointed out to me.
Can anyone help shed any light on this please?
Thanks,
Rob
I have a sheet within which I have a list linked to a MOSS list.
(The file was created in XL2003 and I'm using XL2007, so the
synchronization functionality is working just fine)
I have another sheet on which I wish to filter selected records from
the MOSS-linked list.
The following code is called from the 'receiving' sheet:
Sub Advanced_Filter_Due_To_Complete_Projects()
Sheets("Projects Due For Completion").Activate
Range("H2").Value = ">=" &
Format(Range("To_Complete_Date_1").Value,"dd/mm/yyyy")
Range("I2").Value = "<=" &
Format(Range("To_Complete_Date_2").Value,"dd/mm/yyyy")
ActiveSheet.Range(Range("B5").End(xlDown),
Range("B5").End(xlToRight).Offset(1, 0)).ClearContents
Range("'MOSS Projects In Progress'!
Projects_In_Progress").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("H1:I2"),
CopyToRange:=Range("B5:E5"), Unique:=False
End Sub
The 'destination' sheet is 'Projects Due For Completion'
The 'source' sheet is 'MOSS Projects In Progress'
The MOSS-linked list is named 'Projects_In_Progress'.
If I record a macro whilst performing the filter, obviously everything
works just fine. Running the code afterwards though produces no result
at all.
I've tried qualifying the ranges in a variety of different ways
(sheets(...).range(...), range(...) etc.), but no joy.
I've also changed the criteria from being calculated as a formula to
being created as a string value and entered (as in the code above). I
read on another thread that it may be something to do with string
manipulation of date fields by XL? (the format on the source sheet
list is 'dd/mm/yyyy' as in the code above)
I can only guess as to why its not working - maybe something to do
with the list/'name' in XL2007? I have tried creating a dynamic range
name for the list "MOSS_Projects_In_Progress" referring to the list
just in case that's it, but that doesn't work either.
Basically, I'm stumped and can't see the solution. No doubt its
something obvious and I'll kick myself once its pointed out to me.
Can anyone help shed any light on this please?
Thanks,
Rob