Worksheet name with space problem

[Guest]

Doing an advanced filter on a worksheet with a space in the worksheet name
causes it to crash even if using a variable such as
ws.range(somerange).advancedfilter.

Anyone know what causes it or work around?

Thanks

 

[Guest]

Go there first:

Sub Macro2()
Sheets("a a").Activate
Range("A1:C10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"K5"), Unique:=True
End Sub

 

[Guest]

Thanks for the reply.
That actually brings up a separate problem/bug documented by Microsoft. The
data is actually filtered to another worksheet which must be activated.

Sorry about leaving that out but I didn't think it was an issue since I was
handling another bug.

Is this particular bug documented somewhere?

Steve

 

[Guest]

I don't know if its a bug or just a limitation. I tried to manually run
Advanced Filter with the Recorder either turned on or off. Manually,
naturally, we are always on the sheet that the filter is using. When I tried
to save to a destination that was on another sheet, Excel generated an error
message saying I can't do it.

 

[Dave Peterson]

Does excel crash when you create a small test workbook.

This didn't crash xl2003 for me:

Option Explicit
Sub testme01()

Dim wks1 As Worksheet
Dim wks2 As Worksheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("name with space1").Delete
Worksheets("name with space2").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set wks1 = Worksheets.Add
With wks1
.Name = "name with space1"
.Range("a1").Value = "header"
.Range("a2:a10").Formula = "=""A""&mod(row(),3)"
End With

Set wks2 = Worksheets.Add
wks2.Name = "Name with space2"

wks1.Range("a1:A10").AdvancedFilter _
action:=xlFilterCopy, _
copytorange:=wks2.Range("a1"), _
unique:=True

End Sub

 

[Dave Peterson]

If you start your data|filter|advanced filter on the "receiving" worksheet, it
should work ok.

 

[Guest]

I don't why you are deleting the worksheets and then adding them.

I have a workbook that contains the code and worksheets for retrieving the
data.

I open up a workbook that contains a worksheet with rows and columns of data
of which one column I want to extract into the first workbook. If the data
worksheet's name has a space in it the autofilter code errors.

Right now I have added code to replace the spaces with the infamous _ but
you shouldn't have to do that unless it is a geniune bug and supposedly your
code works.

This is my code

Set wsEx = ThisWorkbook.Worksheets("ExtractData")
Set wsData = ActiveSheet 'activesheet name could be My Worksheet

wsData.Range("DataCol").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsEx .Range("LDExCrit"), _
CopyToRange:=wsEx .Range("ExtractCol"), Unique:=True

I notice you don't have the CriteriaRange set but that shouldn't affect it.
You also are setting the worksheet variables to the initial add worksheet
code instead of the name of the worksheet such or activesheet which could
make a difference.
I am using named ranges but that shouldn't matter either.

Steve

 

[Dave Peterson]

The code I used deleted worksheets with those test names--if they existed. I
wanted to make sure that if you ran the code twice, it would not end in an error
when the renaming of the new worksheets took place.

No you shouldn't have to rename those worksheets that have spaces in their
names. You may want to try that suggested code in a new workbook to see if
works for you.

Since you haven't posted any real details, I'm guessing that the error is caused
by an error in your code--not because of the space in the worksheet name.