Insert Row in Tables Multiple Sheets Excel 2010

[macvic]

Hi...I have tables in 12 sheets (Jan - Dec) and synchronize them into a YTD table which adds the data from each month by formula. Is there any way to insert a row on all the tables, at once so they remain synched? When I select all the worksheets, I cannot insert rows in the tables ( or in fact do any design changes - like rename a column) " Cannot makes changes to a table or XML mapping when multiple sheets are selected" . What I end up doing is selecting each sheet and making the changes.....Appreciate any help

Thanks

 

[bobhc]

Good day macvic


Not sure how many tabs you have but best way is to select first press shift key and select last tab, all tabs same highlight, title of workbook at top of sheet now shows the word Group, in the first sheet which is still open right click on the row number below where you want the new row and select insert from drop down. all sheets will have a new row, just click on any sheet to remove the group.

 

[Luke M]

@bobhc

Might want to re-read the OP. macvic already tried selecting all the sheets, but it won't let you modify tables. =(

 

[bobhc]

Good day Luke M


Yep missed that bit, time to re think but from memory I do not think it can be done to tables, I think the only way to do this if the OP wants the rows in the table is to convert the tables to a range insert rows and then convert back to tables, but this way is the long way as each sheet needs to be converted to a range

 

[Luke M]

@macvic

Not a perfect solution, but might be worth it to record a macro of you inserting a row in each table on each worksheet. Then, assign that macro to a button, and whenever you need to add a row you would only need to click the button. If you want some help, after you record the macro, post it here and we'll help clean it up and make it more efficient.

 

[bobhc]

Good day Luke M


Most of the ribbon edit tools are greyed out when tabs are grouped. A macro to insert rows works fine on a data range or single table but throws an error when you try to use it on a grouped table. If you group data sheets then the insert table icon are greyed, out so you have to create each table sheet individually or copy via the sheet tab. Editing grouped non-table work sheet works with no problem

I have looked extensively on the web and I cannot find the answer to insert a new row in to grouped sheet tables. As I said in a previous post it’s all about converting back to ranges doing the edits and them making tables again but this all defeats the OP’s question. Perhaps the best solution is to do as you suggested and record a Macro assign a button and the copy the button to all sheets. Would mean clicking the button on each sheet. Would be interesting to see if someone knows of a way to work with grouped sheet tables.

 

[Luke M]

Hi bobhc,


I think we have "failure to communicate". =)


Macro steps

1. Don't use grouped sheet

2. Select sheet

3. Insert row to table

4. Repeat steps 2 and 3 until all tables have rows added.


Save macro, and add to button. So, it does what OP was originally doing, but automatically now, and not so manually tedious. Hope that helps clarify. I agree with what you said, there's no way to add the rows if the sheets are grouped.

 

[bobhc]

Hi Luke M


Is your suggestion not basically the same as " Perhaps the best solution is to do as you suggested and record a Macro assign a button and the copy the button to all sheets. Would mean clicking the button on each sheet."

 

[Luke M]

Close. But not a button on each sheet. Let the macro do that. This should just about do it. Select the cell in table. Tables will get a row added above where the active cell is.

[pre]

Sub AddRowTables()
Dim sName As String
Dim myCell As String
Dim myRow As Long

myCell = ActiveCell.Address

'Assumes table header is in row 1. If not, change the
'subtractor to whatever the header row is in
myRow = ActiveCell.Row - 1

Application.ScreenUpdating = True
For i = 1 To 12
'Get month 3 letter name. We put 28 first so
'that system always assumes a dd-mmm-yyyy format
sName = Format(DateValue("28-" & i & "-2013"), "mmm")
With Worksheets(sName)
.Range(myCell).ListObject.ListRows.Add (myRow)
End With
Next i
Application.ScreenUpdating = True

End Sub

[/pre]

 

[bobhc]

Hi Luke M


I understand what you mean it took me awhile. Have been trying your VBA and trying, and failing, to get it to insert a row above the cell high lighted with out the need for table information, is this possible?

 

[Luke M]

I didn't try much to figure out a better way to select cell/row. I just assumed OP would select a cell within the table, and all tables were in same spot (since goal was to do this with multi-sheet). You're welcome to give it a try to find a better method. Me? It's Friday, and it's almost time to head off and relax. =)

 

[bobhc]

I have be trying these routines out and the first two work ok but the third is causing me problems. Also when row(s) are inserted in to the table the zebra stripes are lost, what is the VBA to keep these lines when new ones are inserted?

[pre]

Sub Insert_Lines_At_Cursor()
Answer = InputBox("How many lines to insert? (20 lines maximum)")
NumLines = Int(Val(Answer))
If NumLines > 20 Then
NumLines = 20
End If
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumLines
EndInsertLines:
End Sub
Next routine

[pre][code]Sub Insert_Lines_At_Cursor2()
On Error Resume Next
ActiveCell.EntireRow.Resize(Int(InputBox("How many lines to insert?"))).Insert
End Sub

[/pre]
Both of the above work but only on the active sheet I have tried the routine below to insert the rows on all work sheets but I am getting this compile/syntax error


For Each ws In ThisWorkbook.Worksheets ws.Activate

Would appreciate some pointers on this. :-

Sub Insert_Lines_At_Cursor3()
Dim ws As Worksheet
Answer = InputBox("How many lines to insert? (20 lines maximum)&#34
NumLines = Int(Val(Answer))
If NumLines > 20 Then
NumLines = 20
End If
For Each ws In ThisWorkbook.Worksheets ws.Activate
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumLines
EndInsertLines:
End Sub[/code][/pre]

 

[Luke M]

Time for a Carlsberg my friend. =)

Change this:

For Each ws In ThisWorkbook.Worksheets ws.Activate

To this:

[pre][code]For Each ws In ThisWorkbook.Worksheets
ws.Activate[/pre]
Can't do 2 commands on the same line like that.


As for the zebra stripes, that's the difference between

Range(myCell).ListObject.ListRows.Add (myRow)

and

Selection.EntireRow.Insert[/code]


The first just adds a row to the ListObject (aka, table), while the latter adds a row to entire worksheet.

 

[bobhc]

Hi Luke M

Defiantly time for a Carlsberg, I am the process of making the VBA a mess. I am now getting a "Run-time error 1004 Method ‘Range’ of object ’_Global’ failed" error on ethis line of code "Range(myCell).ListObject.ListRows.Add (myRow)"

[pre]

Sub Insert_Lines_At_Cursor4()
Dim ws As Worksheet
Answer = InputBox("How many lines to insert? (20 lines maximum)")
NumLines = Int(Val(Answer))
If NumLines > 20 Then
NumLines = 20
End If
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Range(myCell).ListObject.ListRows.Add (myRow)
Count = Count + 1
Loop While Count < NumLines
Next
EndInsertLines:
End Sub

[/pre]

 

[Luke M]

Your macro doesn't define what

myCell or [code]myRow

are. If just copying from my macro above, they were defined as so:

[pre]Dim myCell As String
Dim myRow As Long

myCell = ActiveCell.Address

'Assumes table header is in row 1. If not, change the
'subtractor to whatever the header row is in
myRow = ActiveCell.Row - 1[/code][/pre]