logo
down
shadow

ShowAllData method of Worksheet class failed


ShowAllData method of Worksheet class failed

Content Index :

ShowAllData method of Worksheet class failed
Tag : excel , By : user185144
Date : November 29 2020, 01:01 AM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Select Method of Worksheet Class Failed


Tag : excel , By : Joe Sweeney
Date : March 29 2020, 07:55 AM
wish helps you I don't know what GWP is, but I think you want to use ProjNo there. The Range property doesn't accept an argument like that. Unless you have a named range of "Row,6" which you don't because it's not a legal name, you have to supply Range with a valid range reference, like A6 or D2:D12, for example.
Also, you can't concatenate rows and use them in a Range reference to get a larger range. You would have to copy each row inside the loop, union the ranges as you go, or better yet, filter on the value that you want and copy the visible rows.
Private Sub btnNext_Click()

    With ThisWorkbook.Worksheets("Sheet7")
        'filter for the project id
        .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 6).AutoFilter 1, "=" & .Range("D6").Value

        'copy the visible rows
        .Range("F2", .Cells(.Rows.Count, 6).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
            ThisWorkbook.Worksheets("Sheet1").Cells(19, 5)

        'get rid of the filter
        .AutoFilterMode = False

    End With

End Sub

Excel VBA - ShowAllData method of Worksheet Class failed


Tag : excel , By : alexmajy
Date : March 29 2020, 07:55 AM
should help you out I have automated a proper record input into the table that I use as a database, and when the table is filtered the input don't work. , Here is the working solution I went with :
Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean

CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name

    Sheets("DB").Activate
    Sheets("DB").Range("A1").Activate
    On Error Resume Next
    If Sheets("DB").FilterMode = True Then Sheets("DB").ShowAllData
    On Error GoTo 0

    DoEvents
    Sheets(ActiveS).Activate

Application.ScreenUpdating = CurrScreenUpdate
End Sub
Private Sub TEST_UnFilter_Table()
    Dim tB As Workbook, _
        Sh As Worksheet

    Set tB = ThisWorkbook
    Set Sh = tB.Sheets("DB")

    Call UnFilter_Table(Sh, "Db_Val")
End Sub
Public Function UnFilter_Table(ByRef SheetWithTable As Worksheet, ByVal RangeName As String) As Boolean

On Error GoTo ErrHdlr
    Dim aWB As Workbook, _
        ActiveSH As Worksheet, _
        ScreenUpdateState As Boolean, _
        StatusBarState As Boolean, _
        CalcState As XlCalculation, _
        EventsState As Boolean, _
        DisplayPageBreakState As Boolean

    Set aWB = ActiveWorkbook
    Set ActiveSH = aWB.ActiveSheet

    DisplayPageBreakState = ActiveSH.DisplayPageBreaks
    ActiveSH.DisplayPageBreaks = False

    With Application
        ScreenUpdateState = .ScreenUpdating
        StatusBarState = .DisplayStatusBar
        CalcState = .Calculation
        EventsState = .EnableEvents

        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With


    SheetWithTable.Activate
    SheetWithTable.Range(RangeName).Cells(1, 1).Activate
On Error GoTo 0

On Error Resume Next
    If SheetWithTable.FilterMode Then SheetWithTable.ShowAllData
On Error GoTo 0

On Error GoTo ErrHdlr
    DoEvents
    ActiveSH.Activate
    ActiveSH.DisplayPageBreaks = DisplayPageBreakState

    With Application
        .ScreenUpdating = ScreenUpdateState
        .DisplayStatusBar = StatusBarState
        .Calculation = CalcState
        .EnableEvents = EventsState
    End With

    UnFilter_Table = True
On Error GoTo 0

Exit Function
ErrHdlr:
UnFilter_Table = False
Debug.Print "Error in unfiltering sheet " & SheetWithTable.Name & " !" & vbCrLf & _
            "Error n° " & Err.Number & vbCrLf & _
            Err.Description
End Function

Run-time error '1004' ShowAllData method of Worksheets class failed


Tag : excel , By : Adil
Date : November 01 2020, 03:09 PM
like below fixes the issue It depends of the state of FilterMode.
Personally, I made this (user won't see anything happening) to cancel filters on a sheet :
Public Sub UnFilter_Tables_On_Sheet(Sheet_Name As String)
Dim ActiveS As String, CurrScreenUpdate As Boolean

CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name

    Sheets(Sheet_Name).Activate
    Sheets(Sheet_Name).Range("A1").Activate
    On Error Resume Next
    If Sheets(Sheet_Name).FilterMode = True Then Sheets(Sheet_Name).ShowAllData
    On Error GoTo 0

    DoEvents
    Sheets(ActiveS).Activate

Application.ScreenUpdating = CurrScreenUpdate
End Sub

Error 1004 - 'select method of worksheet class failed' when worksheet is Selected


Tag : excel , By : cameron
Date : March 29 2020, 07:55 AM
To fix this issue Name, Index and CodeName are three properties of a worksheet, which are quite different. In the code from the screenshot, Sheet21 is a CodeName and it probably corresponds to Worksheets(17).
Worksheet CodeName Property Refer to sheet using codename
Sub TestMe()
    Dim i As Long
    For i = 1 To Worksheets.Count
        Debug.Print Worksheets(i).Name
        Debug.Print Worksheets(i).Index
        Debug.Print Worksheets(i).CodeName
        Debug.Print "-----------------------"
    Next i
End Sub

VBA getting error Active method of Worksheet class failed


Tag : vba , By : user126922
Date : March 29 2020, 07:55 AM
this will help Solution 1:
Instead of Sheets.("Discount").Activate write Sheets("Discount").Activate and it should work. E.g., remove the dot.
Related Posts Related QUESTIONS :
  • Finding the volume of a tank in VBA given the radius, height, and depth
  • Compile Error on simple VBA form and command button click code
  • Looping through Workbooks and Copy a Dynamic range to Master Workbook
  • Using .NET HashTable Return Type in VBA
  • How do I not get Run-time error '13': Type mismatch while iterating rows?
  • How to change image of a Powerpoint shape?
  • Adding character before text in Excel without using formula
  • How can I create a list of numbers that is like the following?
  • How to fix " Run-time error '380' in Excel VBA?
  • How can you repeat a number x times in a column in a continuation, in excel
  • How to delete the content of a txt file with vba?
  • How to work with more Target.Address (range)
  • Change arrow color based on its label or value
  • how to get inner text of html under id?
  • Getting "Argument not optional" error on segment of code
  • can't add 1 to current cell value
  • Role/permission analysis in Excel: comparing list of user permissions with pairs of disallowed roles
  • Excel Range.PivotField equivalent for JavaScript API
  • How to fix 1004 error with macros in Excel VBA?
  • How to copy rows between worksheets based on a text value
  • Even though method exist in sheet
  • How to delete a row if certain cells are blank in VBA
  • how to get address cell
  • Change Worksheet CodeName in protected VBProject
  • Column Offset Excel Formula
  • Convert UTC time stamp in text to Excel date
  • Formula where Number is constant
  • Excel VBA / Split String Into Columns based on Character Count
  • getElementById won't work in VBA, error 438
  • Writing to Excel file in specific format using Perl
  • Filter text out of barcode while scanning physical barcodes
  • Trouble getting custom function syntax right: Contains()
  • Import multiple csv files to specific sheets in a workbook
  • Range.Find Function
  • I need to assign time value to a variable
  • Run event whenever date changes in a cell A1
  • Printing values from a multidimensional array
  • How do I make a cell equal to the value of the cell address returned from another cell?
  • How to loop through a column adding values from a second column?
  • How to create a Excel file with a ColdFusion which has a column which we can sort by Largest to Smallest or vice versa?
  • VBA Dictionary.Exists Method Returning False Negative Given String From String Array
  • Object Defined error when adding data validation
  • How to fix error 1004 on a wb.SaveAs function
  • Trying to split data from one cell into multiple cells
  • Excel VBA : Hyperlink that fills target cell's contents with hyperlinked text
  • Why is VBA function giving compile error for collection but not for string
  • Error 91 Object variable or With block variable not set
  • How to find the sum for customer's multiple payments using Excel formula?
  • Depends of what columns are selected create a new sheet
  • VBA converting formula to comment
  • Clear contents of active cell if it contains specific text
  • While dynamic range
  • How to make "edit links" VBA code run faster?
  • Deleting Rows with Reverse Loop - VB
  • VBA change font properties of chart axes without using With-EndWith Statement
  • Find a string within an Excel file and get the row index
  • Referencing Public Constants in Cell Formulas
  • How to return worksheet or workbook name in cell
  • Why is this VBA function slow?
  • COUNTING COMMISSION
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com