Macro to insert a new row at a variable Line

[ThatMasonGUy]

I'm new to Macros, I'm currently putting together a spreadsheet to track finances. Each row has very little calculations that need to be copied, but I know how to sort that part with a macro so that's no problem.

What I'm trying to do is make a button where I click it, and it adds another row at the bottom of my section, for example below, I'd like to add a row at the bottom, then the button shift down with that row and next time I hit that button it adds a row below it. e.g., insert row at 49 then 50 and so on.

Another problem is there are rows above and below in different sections that I also need to add to, is there a way to lock the button to a certain row, then when a new row is added be it by the button or something else it remains locked on that row?

 

[bebo021999]

Which rows are "Purchase Order" and "Actual Expenses" in?
Could you upload again with row and column address?

 

[ThatMasonGUy]

Which rows are "Purchase Order" and "Actual Expenses" in?
Could you upload again with row and column address?

Hi, thanks for the response

 

[bebo021999]

So, do you want:
If Active cell was in "Purchase Order " range (A45:H67), click button then row 66 was inserted?
Similar, if active cell was in "Actual Expenses" range (A69: H100), with same button, but last row 100 was inserted?

 

[ThatMasonGUy]

So, do you want:
If Active cell was in "Purchase Order " range (A45:H67), click button then row 66 was inserted?
Similar, if active cell was in "Actual Expenses" range (A69: H100), with same button, but last row 100 was inserted?

Not entirely sure what that means, I don't know if its possible.

But I would like something that goes "When this macro was created it inserted a new row above 67. *Now we're a month later and row 67 is actually row 112*, so instead insert next row at line 111"

The difficulty I have with setting a range is that this spreadsheet balloons out massive from where its initially created, at the start "Purchase Order" was (A9:H16) and Actuals was (A18:H25).

 

[bebo021999]

Try XL2BB tools to upload a mini sheet.
how?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[ThatMasonGUy]

I ended up creating a template tab where I use that to copy the relevant row from, and paste it above text that is hidden below the button.

Here is the macro:
Sub NewActual()

Dim currentActiveWorksheet As String
currentActiveWorksheet = ActiveSheet.Name

Cells.Find(What:="NewActual", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

Rows(ActiveCell.Row).Select

Sheets("Template").Select
Rows("38:38").Select
Selection.Copy

Sheets(currentActiveWorksheet).Select

Selection.Insert Shift:=xlDown
Cells.Find(What:="NewActual", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

End Sub

Here is the button and hidden text: