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]

If indeed you have two Tables on your worksheet, you might give the following a try...

Sub InsertTableRow()
Dim i As Long
For i = 1 To 2
    Sheets("SF Client Grid 24mo Fcst").ListObjects(i).ListRows.Add (1)
Next i
End Sub

Cheers,

tonyyy

 

[wakefield101]

Thanks Tony!

However they are not technically "tables"... That is why I was referring to them as "unformulated tables"...... Does this make sense?

 

[tonyyy]

So, you didn't use the Insert / Table command, right? (And why not?? [just kidding])

Sounds like you have two ranges, each with a header row. The header row of the first range is in Row 12 - please confirm.

As for the second header row, we'll have to find it. What's the value in the first and second column of the second header row?

 

[wakefield101]

Hi Tony, sorry for the delayed response.

There are too many complications, that is why I am not using tables.

Yes, I do have two ranges, each with a header.

I just created an example model with the code that I am using in my actual model, however I just realized that I cannot add an attachment....... hum do you have any thoughts on how I can share my example with you and/or this thread in general??

Let me know what you think.

 

[tonyyy]

I just created an example model with the code that I am using in my actual model, however I just realized that I cannot add an attachment....... hum do you have any thoughts on how I can share my example with you and/or this thread in general??

Refer to the Guidelines for Forum Use...

 

[wakefield101]

Okay, I figured it out.

Here is the Macro code that I am currently using. I set it up as a button.


Sub AddRow_24MoGrid()
'
' Add Row_GridFcst Macro
'

Sheets("Sheet1").Rows("6:6").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

Sheets("Sheet1").Rows("16:16").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

End Sub

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
3	3	C	D	E	F	G	H	I	J	K	L
4	4	*	*	*	*	*	*	*	*	*	*
5	5	*	Unit No.	Unit Name	Unit Basis	Unit Owner	Contract Value *	Sold Unit Price	Total Sold Units	*	Add Row Button
6	6	*	*	*	*	*	*	*	*	*	*
7	7	*	*	*	*	*	*	*	*	*	*
8	8	*	*	*	*	*	*	*	*	*	*
9	9	*	*	*	*	*	*	*	*	*	*
10	10	*	*	*	*	*	*	*	*	*	*
11	11	*	*	*	*	*	*	*	*	*	*
12	12	*	*	*	*	*	*	*	*	*	*
13	13	*	*	*	*	*	*	*	*	*	*
14	14	*	Unit No.	Unit Name	Unit Basis	Unit Owner	Contract Value *	Sold Unit Price	Total Sold Units	*	*
15	15	*	*	*	*	*	*	*	*	*	*
16	16	*	*	*	*	*	*	*	*	*	*
17	17	*	*	*	*	*	*	*	*	*	*
18	18	*	*	*	*	*	*	*	*	*	*
19	19	*	*	*	*	*	*	*	*	*	*
20	20	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[wakefield101]

Disregard the *'s

That came from the upload..

 

[tonyyy]

wakefield101,

I made a few changes to your sheet...
*Deleted all merged cells (vba 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...

Sub AddRow_24MoGrid()
Dim FoundHeader As Range

Sheets("Sheet1").Rows("6:6").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

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

 

[wakefield101]

Thanks Tonyy,

To test your code out, I applied it to my example model. However, I received an error "Run-time error '91' Object variable or With block variable not set"

Once I clicked Debug, VBA highlighted the entire row: "FoundHeader.Offset(1,0).EntireRow.Select"

Any idea on how to fix this?