It would be a lot easier to comment on code that we can see, but the most likely cause would be that, since you're using a commandbutton and not a Form button, your code is in the worksheet code module. That means that any references to Range or Cells that don't refer specifically to a sheet (or the Application object) are referring to cells on the sheet containing the code/button, regardless of which sheet is active. In other words, a reference like Range("A1") will always refer to A1 on the sheet containing the code, whereas you should be using Sheets("Sheet name").Range("A1") explicitly.

Here is the code I use, when I assign it to a form button, it doesn't clear "all data" sheet and paste the data from "export" sheet.

can anyone shed any light on it?

Sub Clear_Existing_Data_Before_Paste()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set wsCopy = Worksheets("Export")
  Set wsDest = Worksheets("All Data")
    
    
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
      
  
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    
    wsDest.Range("A2:D" & lDestLastRow).ClearContents

    
    wsCopy.Range("A2:D" & lCopyLastRow).Copy _
      wsDest.Range("A2")

End Sub

Assuming you assigned it to the form button correctly, put a breakpoint at the start of the code then click the button and step through the code checking the variable values.

Thanks! If I only want to copy visible cells on sheet “Export”, how can I change the code? Because the data I want to copy is filtered on sheet “Export”. 

The copy line should only copy unfiltered rows anyway, but if you need to specify it, use:

wsCopy.Range("A2:D" & lCopyLastRow).Specialcells(xlcelltypevisible).Copy _
      wsDest.Range("A2")

thank you very much!