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

wakefield101

New Member
Joined
Jan 4, 2014
Messages
31
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
 
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...
 
Upvote 0
This is the line that looks for the phrase "Unit No."

Code:
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."?
 
Upvote 0
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
 
Upvote 0
wakefield,

Code:
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?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top