1

I have a worksheet that allows a user to pastes data in and then paste it into a hidden sheet in the workbook that drives a pivot table. This is done by command button.

My code selects data in the range until the last row, then pastes it into the next row without data in it in the other worksheet. I am then trying to delete the data from the upload sheet once then has finished. Everything works beside the deletion of the data. Not sure whats wrong. I also tried to use 'Cut' instead of copy, but had the same result. SOme help would be appreciated.

Sub TestingPaste()

    Application.ScreenUpdating = False
    Dim lst As Long

    Sheets("Upload Data").Range(Range("A2:N2"), Range("A2:N2").End(xlDown)).Copy
    With Sheets("Test")
        lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lst).PasteSpecial xlPasteColumnWidths
        .Range("A" & lst).PasteSpecial xlPasteValues
        Sheets("Upload Data").Range(Range("A2:N2"), Range("A2:N2").End(xlDown)).Clear
    End With

    Application.ScreenUpdating = True

End Sub
4
  • What happens if you move the Sheets("Upload Data" ... line outside the With Sheets("Test") block?
    – mcalex
    Nov 8 '17 at 2:26
  • Sorry i should have mentioned I tried that too, nothing happened at all. No error either, just seemed to skip over the .clear line
    – user767772
    Nov 8 '17 at 3:32
  • Above code as a standalone Sub (Macro) worked fine on my Excel 2013. Did you try just running a single statement 'Sheets("Upload Data") ... .Clear' as a Sub and see if it clears the rows in first place? Is this the final code as intended or a sample code that you are showing as an example? What is the Code under the Command Button in question? May be start tracing from that end?
    – patkim
    Nov 8 '17 at 8:22
  • Try Sheets("Upload Data").Range(Sheets("Upload Data").Range("A2:N2"), Sheets("Upload Data").Range("A2:N2").End(xlDown)).Copy and Sheets("Upload Data").Range(Sheets("Upload Data").Range("A2:N2"), Sheets("Upload Data").Range("A2:N2").End(xlDown)).Clear
    – BruceWayne
    Nov 8 '17 at 16:00
0

Thanks everyone. @BruceWayne, thanks for the advice. I used some of your suggestion, but found that after the sub had completed it left the user on the 'test' sheet. I have since added Sheets("Upload Data").Select to return the user to the upload sheet. Below is the final result for anyone interested.

Dim lst As Long

Sheets("Upload Data").Range(Range("A2:N2"), Range("A2:N2").End(xlDown)).Copy
With Sheets("Test")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteColumnWidths
    .Range("A" & lst).PasteSpecial xlPasteValues
    Sheets("Upload Data").Range(Sheets("Upload Data").Range("A2:N2"), Sheets("Upload Data").Range("A2:N2").End(xlDown)).Clear
End With

    Sheets("Upload Data").Select

Application.ScreenUpdating = True

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.