Excel Runtime Error 1004 Showalldata
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you excel runtime error 1004 application-defined or object-defined error might have Meta Discuss the workings and policies of this site excel 2013 runtime error 1004 About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or excel runtime error 1004 pivot table posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of
Excel Runtime Error 1004 Sort Method Of Range Class Failed
4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ShowAllData method of Worksheet class failed up vote 15 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) excel runtime error 1004 macro Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4)) wb.Activate If 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 46131024 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 24 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
2004. StargateFanFromWork Guest I have a PRINT ALL macro with button on a sheet. There are 2 other print macros each filtering the data excel runtime error 1004 paste method of worksheet class failed by 2 criteria. The 3rd, however, is to print the entire worksheet
Excel Runtime Error 1004 Autofill Method Of Range Class Failed
without filters and I put the ShowAllData code at the beginning before doing the sort and bringing up
Vba Excel Runtime Error 1004
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 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 http://www.pcreview.co.uk/threads/showalldata-works-when-filtered-error-code-when-all-showing.941006/ are 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
Forum Microsoft Office Application Help - Excel Help forum Excel General [SOLVED] Run time Error '1004 To get replies by our experts at nominal charges, follow http://www.excelforum.com/showthread.php?t=547596 this link to buy points and post your thread in our Commercial Services forum! Here is the FAQ for this forum. + Reply to Thread Results 1 to 9 http://www.office-forums.com/threads/show-all-data.1986490/ of 9 Run time Error '1004 Thread Tools Show Printable Version Subscribe to this Thread… Mark this thread as unsolved… Rate This Thread Current Rating Excellent Good runtime error 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 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 runtime error 1004 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 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
complex filters and am trying to make the spreadsheet more user friendly. I have included a button to restore the Show All Data using the VBA: ActiveSheet.ShowAllData This works, but only if there has been a filter applied. If the user clicks the button again (without a filter being present) I get the following error message - "Run Time error 1004 - ShowAllData method of Worksheet class failed". How can I stop this error message, or better still advise the user (MSGBOX?) that all the data is currently being shown? Thank you for your help. B Baggins, Jul 2, 2007 #1 Advertisements Gary Keramidas Guest maybe something like this If ActiveSheet.AutoFilterMode = True Then ActiveSheet.ShowAllData End If -- Gary "B Baggins" <> wrote in message news:... >I have some quite complex filters and am trying to make the spreadsheet more > user friendly. > > I have included a button to restore the Show All Data using the VBA: > > ActiveSheet.ShowAllData > > This works, but only if there has been a filter applied. If the user clicks > the button again (without a filter being present) I get the following error > message - "Run Time error 1004 - ShowAllData method of Worksheet class > failed". > > How can I stop this error message, or better still advise the user (MSGBOX?) > that all the data is currently being shown? > > Thank you for your help. Gary Keramidas, Jul 2, 2007 #2 Advertisements Tom Ogilvy Guest if activesheet.filtermode then activesheet.showAlldata End if -- Regards, Tom Ogilvy "B Baggins" wrote: > I have some quite complex filters and am trying to make the spreadsheet more > user friendly. > > I have included a button to restore the Show All Data using the VBA: > > ActiveSheet.ShowAllData > > This works, but only if there has been a filter applied. If the user clicks > the button again (without a filter being present) I get the following error > message - "Run Time error 1004 - ShowAllData method of Worksheet class > failed". > > How can I stop this error message, or better still advise the user (MSGBOX?) > that all the data is currently being shown? > > Thank you for your help. Tom Ogilvy, Jul 2, 2007 #3 Gary Keramidas Guest sorry, should have posted this If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData End If -- Gary "Gary Keramidas"