Solved: not able to show all data autofilter problem

[mea99sdp]

hi
i'm having problems with this code, the code search for word then does copy and past to another workbook, then repeat for another word, but at the end the showalldata does not seem to work with it.

any ideas why.
cheers


Set i = Range("b:B").Find(" ND", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
Columns("B").AutoFilter Field:=1, Criteria1:="ND"
Range("A2").CurrentRegion.Copy
Workbooks("Histogram").Activate
Worksheets("ND").Activate
If IsEmpty("A1") Then
ActiveCell.PasteSpecial
Else
Columns("A").End(xlDown).Offset(1, 0).PasteSpecial
End If
Workbooks(2).Activate
ActiveSheet.ShowAllData

[Bob Phillips]

You switched workbooks before you issued the command.

[mea99sdp]

yes i believe so, i can do other commands, but it seens not to show all data or turn off filters.

for example if it change
Workbooks(2).Activate
columns("B").select
ActiveSheet.ShowAllData

it does select the work book i want with the autofilter already enables but it still does not turn it off or show all data. So i know it's in the right workbook, but it seems not to like the command or ignors it

[Bob Phillips]

The activesheet is in the new workbook when you switch workbooks.

[mea99sdp]

sorry i not sure what you mean

the macro is saved in a main file call histogram, then when i open a cvs file (names based on date) i run the macro on that file, there is only 2 excel files open. the 1st part of the macro runs great, until i get to the second part, after applying the autofilter for "NT" the filter is not removed at the end, so it can find "NTA" as it is still hidden/filtered. That my problem. I know i'm back in the cvs file but the filters stay on.

Does this help





'the 1st bit of the macro prepares the data

Dim b As Range
'Delete WI data
On Error GoTo veryend1
Set b = Range("AD:AD").Find(" Test", LookIn:=xlValues, lookat:=xlWhole)
If Not b Is Nothing Then
Columns("AD:AO").Delete
Range("AD1").Delete (xlShiftToLeft)
End If
veryend1:
Err.Clear
'Delete blank rows
Columns("B").Select
On Error GoTo Veryend2
Selection.AutoFilter Field:=1, Criteria1:=""
Rows.Delete
Veryend2:
Err.Clear
Rows("1").Insert

'Delete Columns O-P
Columns("o:P").Delete
'Insert Column & Round Down Pmax
Columns("AA").Insert Shift:=xlToRight
'Range("AA2").FormulaR1C1 = "=rounddown(rc[-10],0)"
Range("AA2").Activate
ActiveCell.FormulaR1C1 = "=rounddown(rc[-10],0)"
Do While ActiveCell.Value > "0"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=rounddown(rc[-10],0)"
Loop
ActiveCell.Delete



'the next bit sorts of 20+ different words and if finds filters then copys back to main histogram file
Dim i As Range
'NT
On Error GoTo Veryend3
Set i = Range("b:B").Find(" NT", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
Columns("B").AutoFilter Field:=1, Criteria1:="NT"
Range("A2").CurrentRegion.Copy
Workbooks("Histogram").Activate
Worksheets("NT").Activate
If IsEmpty(A1) Then
ActiveCell.PasteSpecial
Else
Columns("A").End(xlDown).Offset(1, 0).PasteSpecial
End If
Workbooks(2).Activate
ActiveSheet.ShowAllData
End If
Veryend3:
Err.Clear
'NTA
On Error GoTo Veryend4
Set i = Range("b:B").Find(" NTa", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
Columns("B").AutoFilter Field:=1, Criteria1:="NTA"
Range("A2").CurrentRegion.Copy
Workbooks("Histogram").Activate
Worksheets("NTA").Activate
If IsEmpty(A1) Then
ActiveCell.PasteSpecial
Else
Columns("A").End(xlDown).Offset(1, 0).PasteSpecial
End If
Workbooks(2).Activate
ActiveSheet.ShowAllData
End If
Veryend4:
Err.Clear

[lucas]

Could you post this workbooks. If you could give a before and after that would help too.

click on post reply at the bottom left of the last post...when the page comes up after you type your message scroll down till you find.."Manage Attachments" to attatch your file to the post.

[lucas]

You might try this line which will toggle the autofilter on and off.
Selection.AutoFilter

[mea99sdp]

thank you that solved it.

[lucas]

Hi Mea,
Mark your thread solved using the thread tools at the top of the page.