Activesheet.showalldata Runtime Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta runtime error 1004 vba Discuss the workings and policies of this site About Us Learn more runtime error 1004 excel 2010 about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack runtime error 1004 application-defined or object-defined error Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping
Runtime Error 1004 Excel 2013
each other. Join them; it only takes a minute: Sign up ShowAllData method of Worksheet class failed up vote 12 down vote favorite 3 I notice my VBA script doesn't work when there's an autofilter already on. Any idea why this is? wbk.Activate Set Criteria = Sheets("Sheet1").Cells(i, 1) Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4)) wb.Activate If runtime error 1004 method range of object _global failed ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter, but it crashes on this line Selection.AutoFilter Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value rng.Copy Range("$BC$2:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial Many thanks excel vba autofilter share|improve this question asked Aug 14 '13 at 7:52 Kris Van den Bergh 44131024 1 What error are you getting on the line it crashes on? –Martin Parkin Aug 14 '13 at 7:57 A runtime error: ShowAllData method of Worksheet class failed –Kris Van den Bergh Aug 14 '13 at 7:58 add a comment| 4 Answers 4 active oldest votes up vote 23 down vote AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering). I had the same issue and got it working with If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on. The second catch Or ActiveSheet.Filt
2004. StargateFanFromWork Guest I have a PRINT ALL macro with button on a sheet. There are 2 other print macros each filtering the data by runtime error 1004 select method of range class failed 2 criteria. The 3rd, however, is to print the entire worksheet without
Runtime Error 1004 Pastespecial Method Of Range Class Failed
filters and I put the ShowAllData code at the beginning before doing the sort and bringing up the
Runtime Error 1004 Select Method Of Worksheet Class Failed
print dialogue box:\ ActiveSheet.ShowAllData Application.Goto Reference:="R1C4" Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Range("B2").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Application.Dialogs(xlDialogPrint).Show If the data is already http://stackoverflow.com/questions/18226045/showalldata-method-of-worksheet-class-failed in ShowAll mode, I get the usual 1004 error: "Run-time error '1004': ShowAllDate method of Worksheet class failed" while the entire macro works fine if the sheet is filtered. How can I get around the problem when it happens to _already_ show all the data, pls? Thank you! StargateFanFromWork, Jun 29, 2004 #1 Advertisements Dave Peterson Guest Here are http://www.pcreview.co.uk/threads/showalldata-works-when-filtered-error-code-when-all-showing.941006/ a couple of ways: on error resume next activesheet.showalldata on error goto 0 or with activesheet if .filtermode then .showalldata end if end with StargateFanFromWork wrote: > > I have a PRINT ALL macro with button on a sheet. There are 2 other print > macros each filtering the data by 2 criteria. The 3rd, however, is to print > the entire worksheet without filters and I put the ShowAllData code at the > beginning before doing the sort and bringing up the print dialogue box:\ > > ActiveSheet.ShowAllData > Application.Goto Reference:="R1C4" > Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") > _ > , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= > _ > False, Orientation:=xlTopToBottom > Range("B2").Select > Selection.End(xlDown).Select > ActiveCell.Offset(1, 0).Select > Application.Dialogs(xlDialogPrint).Show > > If the data is already in ShowAll mode, I get the usual 1004 error: > > "Run-time error '1004': > ShowAllDate method of Worksheet class failed" > > while the entire macro works fine if the sheet is filtered. How can I get > around the problem when it happens to _already_ show all the data, pl
Forum Microsoft Office Application Help - Excel Help forum Excel General Run time Error '1004 To get replies by our experts at nominal charges, follow this link to buy points and post your thread in our Commercial Services forum! Here is the FAQ for this http://www.excelforum.com/showthread.php?t=547596 forum. + Reply to Thread Results 1 to 9 of 9 Run time Error '1004 Thread Tools Show Printable Version Subscribe to this Thread… Mark this thread as solved… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 06-01-2006,03:55 PM #1 Jacob Guest Run time Error '1004 I have a macro to run right before the workbook closes. It is as runtime error follows: Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("A1").Select ActiveSheet.ShowAllData ActiveWorkbook.Save End Sub The top row has the autofilter on. If the data is not filtered then when the macro runs it throws a Run Time Error '1004'. If it is filtered the Macro runs fine. I can't seems to find the right code to get the macro to end if it errors out. I think it would be an if then statement but my coding skills aren't quite up runtime error 1004 the par. Any help would be great. Thanks in advance. Jacob Register To Reply 06-01-2006,04:25 PM #2 Dave Peterson Guest Re: Run time Error '1004 With ActiveSheet If .FilterMode Then .ShowAllData End If End With Will avoid the problem--as will: on error resume next activesheet.showalldata on error goto 0 And I think I'd be more specific than using Activesheet. Maybe: With me.worksheets("sheet9999") .select .range("a1").select If .FilterMode Then .ShowAllData End If End With just in case the wrong sheet was active. And I'd use: Me.Save instead of: ActiveWorkbook.Save Me refers to the thing holding the code--in this case, it's ThisWorkbook. ======== But what happens if a user opens the workbook, messes it up beyond belief and wants to close without saving? Your code will save his/her changes and that may not be good. Have you thought of putting the equivalent code in the Workbook_open event so that things are set up when the workbook opens instead? Jacob wrote: > > I have a macro to run right before the workbook closes. It is as > follows: > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > Range("A1").Select > ActiveSheet.ShowAllData > ActiveWorkbook.Save > End Sub > > The top row has the autofilter on. If the data is not filtered then > when the macro runs it throws a Run Time Error '1004'. If it is > filtered the Macro runs fine. > > I c