Using macro button how to insert rows in two non formatted tables

[wakefield101]

I have two unformatted tables in my excel worksheet. The tables are vertical from one another. I have successfully created a macro button that adds 1 row under the header of the first table. However, adding a row to the second table simultaneously is where I am having trouble.

What I want to accomplish: Create a button that will add row below the header of unformatted table 1 THEN add a row below the header of the second table.
Goal: If I need to add 10 lines to each table, all I need to do is click my add row button ten time.

The issue that I am having is that the row reference for table 2 is not adjusting when a new row is added to table 1. When I insert a row in table 1 it shifts all data down one row. Then Table 2 is referencing a row above where it should be rather than adjusting to the change.

Does anyone know how to resolve this issue?


Here is my current VBA Code.

Sub AddRow_24MoGrid()
'
' Add Row_GridFcst Macro
'

Sheets("SF Client Grid 24mo Fcst").Rows("13:13").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

Sheets("SF Client Grid 24mo Fcst").Rows("149:149").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

End Sub

 

[tonyyy]

The error is a result of not finding the header. Did you make the adjustments to your worksheet?

I made a few changes to your sheet...
*Deleted all merged cells (<acronym title="visual basic for applications">vba</acronym> handles merged cells very poorly)
*Placed "Unit" and "No." into a single cell, and
*Increased the row height of the header rows to 30.

If those changes are okay with you then you might give the following a try...

 

[wakefield101]

The only adjustment that I had to make was the row height. None of the cells were previously merged..

 

[tonyyy]

This is the line that looks for the phrase "Unit No."

Set FoundHeader = Sheets("Sheet1").Columns(4).Find("Unit No.", After:=Range("D6"), _
    LookIn:=xlValues, LookAt:=xlWhole, searchdirection:=xlNext)

It looks on the sheet named "Sheet1" in Column D starting after Row 6. Could there be an extra space(s) in the cell that contains "Unit No."?

 

[wakefield101]

Eureka!! okay, now let me try and adapt this code to my actual model...

thank you Tonyy

 

[tonyyy]

You're welcome. Glad it worked out...

 

[wakefield101]

Hey Tonyyy,

I hope you don't mind but I have one trick to ask you about.

So now my two "tables" are inserting a row, however I need this new row to copy the format/formulas from the rows below it.... It is exactly like, I manually copying an entire row and then I inserting the copy row.

In my original code, the one that didn't insert a row in the second table, the inserted row in the first table, was a recorded macro that copied one row and then pasted it above the copied row.

In our new code that you designed, I need it to pretty much do that.

I hope this isn't confusing...

Let me know if you have any ideas..

Thanks for everything

 

[wakefield101]

Hi Tonyyy,

My e-mails indicated that you sent a reply however, nothing is showing up in my message board....

 

[tonyyy]

wakefield,

Sub AddRow_24MoGrid()
Dim FoundHeader As Range

Sheets("Sheet1").Rows("6:6").Select
[COLOR=#ff0000]Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False[/COLOR]

Set FoundHeader = Sheets("Sheet1").Columns(4).Find("Unit No.", After:=Range("D6"), _
    LookIn:=xlValues, LookAt:=xlWhole, searchdirection:=xlNext)
FoundHeader.Offset(1, 0).EntireRow.Select
[COLOR=#ff0000]Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False[/COLOR]
End Sub

You'll notice the code uses the exact same lines as in your original post. Could your spreadsheet somehow have lost its format and formulas?