I suggest NOT to put in sheet or workbook code.
park the macro in the module and run it when necessary
if you want you can create a button from form toolbar (view-toolbar-form)
and assign this macro to it
see the comments in the macro
Sub test()
Dim ws As String, dest As Range
With Worksheets("sheet1")
.Range("A1:C10").Copy
ws = InputBox("type sheet name in which you want to paste e.g. Sheet3")
With Worksheets(ws)
'ENSURE THAT THIS SHEET IS AVAILABLE OTHERWISE OPEN AND GIVE A NAME
'the sheet name should be exact in input box
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
End With
End Sub
Bookmarks