0

The new sheet did not display the whole data after I click "All" button once to display all the data in new sheet after clearing the filter. However, it only shows the whole data when I click "All" button twice.

Here is my code:

Sub Reset1_button()

Application.ScreenUpdating = False
Dim LastRow As Long
Dim LRow As Long

LastRow = WorksheetFunction.Max(Sheets("SALES").Cells(Rows.Count, "A").End(xlUp).Row, 2)
LastRow = LastRow + 1
LRow = WorksheetFunction.Max(Sheets("ONE_ALLIANZ_REPORT").Cells(Rows.Count, "A").End(xlUp).Row, 18)
LRow = LRow + 1

Range("A19:AQ" & LRow).Clear
Sheets("SALES").Select

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

Sheets("SALES").Select
ActiveSheet.Range("A3:AQ" & LastRow).Copy
Sheets("ONE_ALLIANZ_REPORT").Select
ActiveSheet.Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select

End Sub
Jojo V
  • 3
  • 4
  • 1
    @HanaVarsy, Welcome to SO, I would suggest you to avoid `Select` – TourEiffel Sep 18 '19 at 12:34
  • 2
    Read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code. If your issue is still there, please [edit] your question and udpate the code to the new code (without select). – Pᴇʜ Sep 18 '19 at 12:37
  • Thanks !! You just saved my life :) – Jojo V Sep 18 '19 at 16:37
  • @HannaVarsy May I ask you to accept [this answer](https://stackoverflow.com/a/57993018/11167163) . Please look [Here to see how to accept](https://meta.stackexchange.com/a/5235) – TourEiffel Sep 18 '19 at 17:48

1 Answers1

2

You might try this code and tell me if your issue is solved, i think that select was the issue.

Also please note that Thisworkbook appoint the workbook where the macro is coded

Sub Reset1_button()

Application.ScreenUpdating = False
Dim LastRow As Long
Dim LRow As Long

LastRow = WorksheetFunction.Max(ThisWorkbook.Sheets("SALES").Cells(Rows.Count, "A").End(xlUp).Row, 2)
LastRow = LastRow + 1
LRow = WorksheetFunction.Max(ThisWorkbook.Sheets("ONE_ALLIANZ_REPORT").Cells(Rows.Count, "A").End(xlUp).Row, 18)
LRow = LRow + 1

Range("A19:AQ" & LRow).Clear

If ThisWorkbook.Sheets("SALES").FilterMode Then
   ThisWorkbook.Sheets("SALES").ShowAllData
End If


 ThisWorkbook.Sheets("SALES").Range("A3:AQ" & LastRow).Copy Destination:=ThisWorkbook.Sheets("ONE_ALLIANZ_REPORT").Range("A19")

End Sub
TourEiffel
  • 4,034
  • 2
  • 16
  • 45
  • 1
    Note that `If (ThisWorkbook.Sheets("SALES").AutoFilterMode And ThisWorkbook.Sheets("SALES").FilterMode) Or ThisWorkbook.Sheets("SALES").FilterMode Then` is the same as `If ThisWorkbook.Sheets("SALES").FilterMode Then` – Pᴇʜ Sep 18 '19 at 12:39
  • 2
    FWIW, you can do the copy paste in one go to avoid filling the clipboard (and the use of `CutCopyMode`). Resizing `A19` would be an alternative. – JvdV Sep 18 '19 at 12:39
  • 1
    @Pᴇʜ and jvdV I edited the code thanks to your suggestion – TourEiffel Sep 18 '19 at 12:48