I have been having problems with getting a VBA macro running on one sheet to select a range on another sheet

Question

I have been trying to get a macro to work that decants data from one sheets of an excel workbook to the other sheets.

My macro worked for filtering the "master" sheet to show only the data to be decanted to each relevant tab. and my code to copy the filtered rows worked :-)

However, whenever I switched sheets to select cells in order to clear in the receiving sheet, the code crashed.

I went back to basics and setup a simple workbook with 2 sheets and simple data. I then recorded a macro to clear sheet1 then copy manually filtered data from sheet2 to sheet1. Success it worked :-)

I then setup a command button on Sheet2 and pasted the recorded code into its Commandbutton click sub.

When I try to run the code by clicking the command button, it fails at the first cells.select command! :-(

I haven't worked out yet how to upload the simple test workbook.

I went back to basics and setup a simple workbook with 2 sheets and simple data. I then recorded a macro to clear sheet1 then copy manually filtered data from sheet2 to sheet1. Success it worked :-)

I then setup a command button on Sheet2 and pasted the recorded code into its Commandbutton click sub.

When I try to run the code by clicking the command button, it fails at the first cells.select command! :-(

This is the recorded macro which works:

Sub testcopy()
'
' testcopy Macro
' test copy between sheets
'

'
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents
    Sheets("Sheet2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:D500").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

This is the same code pasted into CommandButton1_Click which fails at the first Cells.Select command

Sub CommandButton1_Click()
'
' testcopy Macro
' test copy between sheets
'

'
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents
    Sheets("Sheet2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:D500").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Error message on crashing is :

Runtime error '1004':

Application-defined or object-defined error

And the debugger highlights the cells.select row

See [How to Avoid Using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It'll make your life so much easier and your code so much faster. — BigBen, Aug 08 '19 at 07:38
Is it an Activex button? The issue might be that the code sits in a sheet module. As BigBen says, avoid Select and these problems vanish. — SJR, Aug 08 '19 at 08:14
Yes it is and the macro code did sit in a Module. So is that a known issue that code in sheet modules runs differently to when you paste it into an ActiveX control? — Luke Past, Aug 08 '19 at 08:18
Because the code sits in a sheet module, without a sheet reference the active sheet is implied (Sheet2). `Sheets("Sheet1").Cells.Select` would work. As an aside, ActiveX controls are known to be buggy anyway so best avoided if possible. (Btw please can you remove your answer below?) — SJR, Aug 08 '19 at 08:30

score 1 · Accepted Answer · answered Aug 08 '19 at 07:52

1

Give a try:

Sub testcopy()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Sheet1")
        .Cells.ClearContents
    End With

    With ThisWorkbook.Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A1:D" & LastRow).Copy ThisWorkbook.Worksheets("Sheet1").Range("A1")
    End With

End Sub

answered Aug 08 '19 at 07:52

Error 1004

7,877
3
23
46

Thank you :-) I'll try that – Luke Past Aug 08 '19 at 07:56
if your are satisfy with the code please mark as an answer to helps others with the same issue. – Error 1004 Aug 08 '19 at 08:11

I have been having problems with getting a VBA macro running on one sheet to select a range on another sheet

1 Answers1