Can you have one macro copy/paste data when source stays same but destination changes based on sheet that is open?

[moonshineal04]

I have a macro to copy and paste a table from one sheet to another. I used macro recording to get this VBA:

Sub CopyPasteNew()
'
' CopyPasteNew Macro
' Copy table from "New Term Deal" to Active/open sheet
'

'
Sheets("NEW Term Deal").Select
Rows("3:27").Select
Selection.Copy
Sheets("Ned Stark").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveWindow.SmallScroll Down:=-9
Application.CutCopyMode = False
End Sub

I assigned this macro to a button and it works for the sheets mentioned in the macro. However, what I want to do is add this button to each tab of the worksheet without having to update/add the macro manually every single time adjusting for the sheet name.

Is there a way to write a vba code that automatically changes the destination name based on what sheet is open/active. So instead of naming Sheets("Ned Stark").Select it knows the sheet I want to paste to is the one currently open?

Here are some screenshots of the test document:
Source

DestinationA:
This one works because the macro assigned to the "Add New Term Deal" button is linked to this sheet. However if I add this button to the next sheet "John Snow", it will continue to add the table to "Ned Stark" and not the open/active sheet "John Snow".

 

[portews]

Try this.
It will save that name of whatever worksheet was selected when you ran the macro. It will then copy from the "NEW Term Deal" sheet back to whatever sheet you ran it from.

Sub CopyPasteNew()
'https://www.mrexcel.com/board/threads/can-you-have-one-macro-copy-paste-data-when-source-stays-same-but-destination-changes-based-on-sheet-that-is-open.1207881/

'dimension the variable
Dim CurrSht As Worksheet
'set the current sheet as the one you have open
Set CurrSht = ActiveSheet

Sheets("NEW Term Deal").Select
Rows("3:27").Select
Selection.Copy
'go back to the sheet you originally had open
CurrSht.Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveWindow.SmallScroll Down:=-9
Application.CutCopyMode = False

End Sub

 

[moonshineal04]

Try this.
It will save that name of whatever worksheet was selected when you ran the macro. It will then copy from the "NEW Term Deal" sheet back to whatever sheet you ran it from.

Sub CopyPasteNew()
'https://www.mrexcel.com/board/threads/can-you-have-one-macro-copy-paste-data-when-source-stays-same-but-destination-changes-based-on-sheet-that-is-open.1207881/

'dimension the variable
Dim CurrSht As Worksheet
'set the current sheet as the one you have open
Set CurrSht = ActiveSheet

Sheets("NEW Term Deal").Select
Rows("3:27").Select
Selection.Copy
'go back to the sheet you originally had open
CurrSht.Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveWindow.SmallScroll Down:=-9
Application.CutCopyMode = False

End Sub

Hi Bill!
Huzzah!!! It worked!!! I am basically trying to figure out how macros work with the record function but that only does so much lol. Thank you so much for responding and teaching me something new

Have a great Wednesday!
Rachel