Buttons/Macros won't work on a copied sheet

[christianrwevans]

Probably an easy one here:
I have recorded simple macros to copy rows from other sheets into a sheet called "Scenario Builder 1". E.g.,:

Sub Thermal_Drying()
Sheets("Thermal Drying").Select
Rows("1:39").Select
Selection.Copy
Sheets("Scenario Builder 1").Select
ActiveCell.Insert Shift:=xlDown
End Sub

However when I make a copy of the sheet (e.g., "Scenario Builder 2"), the macro won't work as the reference is to "Scenario Builder 1". My novice intuition is that I need to define the sheet that hosts the button so that I can return to it to paste the rows, but my searching/skill level has not resulted in success yet.
Figured some of you could probably solve this sleep-walking so: what code should I use to 1) define the button host sheet in a way that returns the focus there to paste, or 2) to accomplish this task using a different approach?

Please and thank-you!

 

[Michael M]

Maybe this way

Sub Thermal_Drying()
ans = Application.InputBox("What number Scenario Builder do you want to use......1 Or 2 ?")
Sheets("Thermal Drying").Rows("1:39").Copy
Sheets("Scenario Builder " & ans).Range("A1").Insert
End Sub

 

[christianrwevans]

Thanks Michael M! Looks like that would do the trick - but do you think there is a way to skip the input component and have the macro automatically recognize where it is being launched from?

 

[Michael M]

Ok, so if your are launching from "Scenario Builder 1...or 2...or 3......." maybe use

Sub Thermal_Drying()
Sheets("Thermal Drying").Rows("1:39").Copy
Activesheet.Range("A1").Insert
End Sub

 

[christianrwevans]

Yes that's the sort of thing I think I'm looking for. Poked around a bit, but can't figure out how to use the active cell as an insert location ("ActiveCell.Insert Shift:=xlDown"). What do you suggest?

I get an error with

Sheets("Thermal Drying").Rows("1:39").Copy
ActiveCell.Insert Shift:=xlDown         'doesn't like this line

Even better would be the ability to search for a cell containing the text "Insert Here" and insert starting at that row. Thanks!

 

[Michael M]

Sub Thermal_Drying()
Sheets("Thermal Drying").Rows("1:39").Copy
ActiveCell.Insert
End Sub

 

[Michael M]

The above will error if the activecell is not in Column "A" !!
This will use the row of whatever cell is the activecell

Sub Thermal_Drying()
Sheets("Thermal Drying").Rows("1:39").Copy
Range("A" & ActiveCell.Row).Insert
End Sub

 

[Michael M]

And this will search for "INSERT HERE"......in Upper Case and insert the rows at the row above the found text

Sub Thermal_Drying()
Dim fr As Long
fr = Cells.Find("INSERT HERE", , xlValues, , xlRows, xlPrevious).Row
Sheets("Thermal Drying").Rows("1:39").Copy
Range("A" & fr).Insert
End Sub

 

[christianrwevans]

And this will search for "INSERT HERE"......in Upper Case and insert the rows at the row above the found text

Sub Thermal_Drying()
Dim fr As Long
fr = Cells.Find("INSERT HERE", , xlValues, , xlRows, xlPrevious).Row
Sheets("Thermal Drying").Rows("1:39").Copy
Range("A" & fr).Insert
End Sub

Bullseye! Super grateful to you Michael M, this works like a charm. Thanks so much for your generosity, patience, and expertise!

 

[Michael M]

Glad to help and thanks for the feedback...