Log In
Register
Home
June 1, 2018
Offline
Hi there
I have a few slicers on one sheet using as filter, then I want to copy filtered data from one sheet to another existing sheet by clicking the button set on filter sheet. The data on the existing sheet needs to get deleted before pasting.
My code is not working with the button, but works when the filtered sheet is active.
can anyone shed some light on fixing up the code?
many thanks!
Trusted Members
Moderators
November 1, 2018
Offline
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.
June 1, 2018
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
June 1, 2018
Offline
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”.
Trusted Members
Moderators
November 1, 2018
Offline
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")
1 Guest(s)
