Using VB for Excel, copying a sheet to another sheet when cell is edited gets an error

Asked Sep 28 '22 at 16:23

Active Sep 28 '22 at 16:30

Viewed 18 times

Using VB for Excel, my objective is that when a cell, any cell is edited in the sheet, the macro copies the entire sheet to a different sheet and then goes back to the original sheet, but I am getting an error at the select statement.

How do I fix this?

Private Sub worksheet_change(ByVal Target As Range)

Cells.Select

    Selection.Copy
    Sheets("Tempsheet").Select
    Range("A1:C1").Select   ***'run time error 1004, select method of range class failed***
    ActiveSheet.Buttons.Add(388.5, 47, 42, 17.5).Select
    ActiveSheet.Paste
    Sheets("MainSheet").Select
End Sub

edited Sep 28 '22 at 16:30

Scott Craner

148,073
10
49
81

asked Sep 28 '22 at 16:23

Benjie Benito

It would be much better to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Sep 28 '22 at 16:24
Need to explain the button... – Tim Williams Sep 28 '22 at 17:14
ignore the button code , it is just copying a button on the main sheet – Benjie Benito Sep 29 '22 at 05:26

Using VB for Excel, copying a sheet to another sheet when cell is edited gets an error

0 Answers0