Strange macro behavior!

[davegb]

With help here, I've written the following macro to take filter
criteria from a selected cell on the source sheet and apply an advanced
filter to a datasheet and put the filtered records on a separate sheet
called "County Records". I've used the same macro, with sheet names and
other things adjusted, on 2 previous source sheets (each source sheet
has a corresponding datasheet).

Sub ReunificationExtract()
'Password used

Dim CtyCode As String
Dim WkSht As Object
Dim PWORD As String
Dim CurRow As Integer
Dim SourceSht As String
Dim Cnt As Integer

Dim CtyCell As Object


PWORD = "dave"
Application.ScreenUpdating = False

HomeSht = ActiveSheet.Name

CurRow = ActiveCell.Row
CtyCode = ActiveSheet.Cells(CurRow, "B")


Set CtyCell = Sheets("Reunification
Records").Columns("c").Find(What:=CtyCode, LookIn:=xlValues)
If Not CtyCell Is Nothing Then

'If Len(Trim(ActiveSheet.Cells(CurRow, "c").Text)) > 0 Then
Set WkSht = ActiveWorkbook.Sheets("Reunification Records")

WkSht.Unprotect Password:=PWORD
Sheets("Reunification Records").Range("aa2") = CtyCode
WkSht.Protect Password:=PWORD

Sheets("County Records").Select
Worksheets("County Records").UsedRange.Clear
Range("a1:e1").Merge
Range("a1").FormulaR1C1 = _
"WARNING: This data will be erased the next time
County Records are extracted. "
With Range("a1").Characters(Start:=1, Length:=78).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With

Range("A2:e2").Merge
Range("A2").FormulaR1C1 = _
"If you wish to save the data, copy and paste it to
another spreadsheet or print it before doing another data extraction."
With Range("A2").Characters(Start:=1, Length:=124).Font
.ColorIndex = 3
Rows("2:2").RowHeight = 25
End With
Range("a2").Select
Selection.WrapText = True

Sheets("Reunification
Records").Range("A1:M192").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Reunification
Records").Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False
Range("A4:E4").Merge
Range("a4") = CtyCode & " County Reunification Records"
With Range("a4").Characters(Start:=1, Length:=78).Font
.FontStyle = "Bold"
.ColorIndex = 10
.Size = 16
End With

Columns("A:M").EntireColumn.AutoFit
Range("A5:M5").Select

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Font.Bold = True
End With

Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFormat Format:=xlRangeAutoFormatList2,
Number:=False, Font _
:=True, Alignment:=False, Border:=True, Pattern:=True,
Width:=False

Rows("5:5").RowHeight = 25

Worksheets("Reunification Records").Range("aa5:aa25").Copy
Destination:= _
Worksheets("County Records").Range("a5") _
.End(xlDown).Offset(2, 0)


Worksheets("County Records").Range("a1").Select
Sheets("County Records").Range("aa4").Value = HomeSht
Else
MsgBox "There are no Reunifications for " & CtyCode & " for SFY
2005 2nd Quarter", vbOKOnly
End If
Application.ScreenUpdating = True

End Sub

For some reason, on the third set of sheets, the macro works with any
of the first 16 rows of the source sheet selected, but hangs up when
any of the rows below 16 are selected (I'm not sure if this is
relevant, just giving all my observations). I've done watches and
stepped through the macro, both on data where it works, and on data
(below row 16) where it doesn't work. For some reason, when it doesn't
run, it doesn't find any filterable data in the source sheet, even when
there's plenty of it there. I've run advanced filter using criteria
copied from the source sheet and it filters just fine. Why does this
macro run on some data in the source sheet, but not on other
selections? Why does it run in 2 other sheets but not, in some cases,
in this one?
I'd appreciate any help. I know the code is crude, I'm very new at VBA.
I'm sure it can be written much more efficiently, and would be
interested in feedback on that as well. But I'm mostly concerned about
why it's hanging up as is so I can learn from my mistakes.
Thanks!

 

[davegb]

I found the error. It was in the datasheet, not the macro. The method
I'd used to define the filter range assumed no blank cells in the
datasheet, which up until now, was true. Am redoing that part of the
macro. Now I'm having problems with a line of code which I'm trying to
use to define the correct filter range.

WkSht.Range(.Range("a1"), .Range("a1").End(xlToRight).End(xlDown)) _
.Name = "FilterRange"

It's hangin up at the second ".Range", right before ("a1").
WkSht has been dimmed as an object. The error message is "Compile
Error: Invalid or unqualified reference". So if it's not an undeclared
variable, what is it. Any ideas?
Thanks for the help.

 

[Dave Peterson]

Those .Range()'s mean that they refer to the previous With object.

Maybe...

with wksht
.Range(.Range("a1"), .Range("a1").End(xlToRight).End(xlDown)) _
.Name = "FilterRange"
end with

But if you have gaps, that .end(xltoright).end(xldown) may cause problems.

Can you pick out a column and row that always has data?

I'm gonna guess that row 1 is headers and always has data.
And I'm gonna guess that column A always has data.

Dim LastRow as long
dim LastCol as long

with wksht
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column

.range("a1",.cells(lastrow,lastcol).name = "filterrange"
end with

 

[davegb]

Thanks, Dave!
I've changed the order of the steps so it goes across the top first,
which, as you said, has no blanks because of the column headers. And
I've made sure that the right column has no blanks. Should take care of
that problem.
I've discovered in discussion with the person feeding me the data from
SPSS that this latest data, and some of the future data, comes from an
entirely different database than what she had given before, and has
blanks and some serious formatting problems when downloaded into Excel.
Blank cells, blank cells that aren't really blank, leading and trailing
spaces, etc. Fortunately, I've gleaned some good programs here and
elsewhere to help with most of this.

As for your code, it won't run. First try, I got a compile error on
filterrange. I dimmed it as a range, and now I get a run error on the
line:
..range("a1",.cells(lastrow,las­tcol).name = "filterrange"

I tried it with and without the quotes. I also turned it around,
because it seemed to me that filterrange should be defined as the range
from a1 to lastrow, lastcol, not the other way around. Is there a time
when you define variables backwards, like here?
I also tried changing it to xltoRight, then xlDown, because it seemed
to me that from A1 I want to go to the right, then down to define the
filterrange, not up and left. Is it just me, or does it seem like
everything in this code is backwards? What am I missing?
Any other ideas?

 

[Dave Peterson]

Oopsie. I left out a closing paren:

This:
..range("a1",.cells(lastrow,lastcol).name = "filterrange"
should be:
..range("a1",.cells(lastrow,lastcol)).name = "filterrange"

Sorry.

 

[davegb]

Thanks, Dave!
The macro runs now, but is selecting data outside the table (other info
located further right in the spreadsheet. Is there a way to only select
the data in the table?

 

[Dave Peterson]

Maybe it's better to work down and to the right:

Option Explicit
Sub test()
Dim LastRow As Long
Dim LastCol As Long
Dim wksht As Worksheet

Set wksht = ActiveSheet

With wksht
LastRow = .Range("a1").End(xlDown).Row
LastCol = .Range("a1").End(xlToRight).Column

.Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange"
End With

End Sub


If this didn't work, can you share the address of the table? And what's
adjacent to it that should be avoided?

For what it's worth, I've always tried to keep my tables on separate
worksheets. If I mix tables (or even other stuff) on that same worksheet, it
becomes just a little more difficult to delete or insert rows or columns.

 

[davegb]

It has occurred to me that this might have been easier had I made 3
sheets for each category (Of course, I didn't know when I started it
would work out this way. After I gave my end-users the original data,
they asked for the back-up records that determined the first round
data.) But it would make sense to create a sheet for user data, a
datasheet for the records, and a macro sheet which would hold the
information that the macros use to create the "Records" sheet based on
the selection in the user sheet. After I get this next round of data
out to them, I'll have some time, I hope, and restructure the whole
thing to make it easier to manage.

 

[Dave Peterson]

Hindsight is 20/20. Or you live and you learn.

Good luck.

It has occurred to me that this might have been easier had I made 3
sheets for each category (Of course, I didn't know when I started it
would work out this way. After I gave my end-users the original data,
they asked for the back-up records that determined the first round
data.) But it would make sense to create a sheet for user data, a
datasheet for the records, and a macro sheet which would hold the
information that the macros use to create the "Records" sheet based on
the selection in the user sheet. After I get this next round of data
out to them, I'll have some time, I hope, and restructure the whole
thing to make it easier to manage.

 

[davegb]

Ain't it the truth!

Hindsight is 20/20. Or you live and you learn.

Good luck.