Loop through multiple sheets to add a row

**E5254730** · 07-09-2024, 04:34 AM

Hello,

I have below code to add row. My data is in table format.

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Sales_Table")
tbl.ListRows.Add 1

Whereas I have multiple sheets in table format. I need to add rows in every sheet.

Above code just adds the row in Active sheet and not in the other sheets. There are other sheets as well, like, Table1, Table 2, Table3 and so on.

Please assist. Thanks!

**Sintek** · 07-09-2024, 04:50 AM

Sub J3v16()
Dim ws As Worksheet
For Each ws In Sheets
    ws.ListObjects(1).ListRows.Add
Next ws
End Sub

**E5254730** · 07-09-2024, 04:57 AM

Does not seems to be working for me.

I have multiple sheets. For instance, just assume, I just want to add the rows in Table1, Table3 and Table4

**E5254730** · 07-09-2024, 05:24 AM

Attaching excel for reference

Each day, I add a new row at row 5 and in A5 cell, yesterday's date is added (excluding weekends)

I need to automatically add a new row and date (as stated above), in Table1, Table3 and Table4.

**Sintek** · 07-09-2024, 05:41 AM

You mentioned nothing about adding dates...only add a new row to existing table in all sheets...
This sample above has only one sheet...?
Go back to the drawing board and once you know what you want then come back to the thread

**jdelano** · 07-09-2024, 05:49 AM

Are there specific sheet names with tables? Which sheet is Table2 on and so on? Your example has but 1 sheet and 1 table.

**E5254730** · 07-09-2024, 05:51 AM

Hello,

I have below code to add row. My data is in table format.

Each day, I add a new row at row 5 and in A5 cell, yesterday's date is added (excluding weekends)

I need to automatically add a new row and date (as stated above), in Table1, Table3 and Table4.


Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Sales_Table")
tbl.ListRows.Add 1

Whereas I have multiple sheets in table format. I need to add rows in every sheet.

Above code just adds the row in Active sheet and not in the other sheets. There are other sheets as well, like, Table1, Table 2, Table3 and so on.

Please assist. Thanks!

**E5254730** · 07-09-2024, 06:01 AM

Above link is been closed due to duplication.

Can advise on this thread

**Sintek** · 07-09-2024, 06:04 AM

Post 6 does just that...adds a new row to table in every sheet...
What has row 5 or cells 5 got to do with this..Please be more transparent...

**E5254730** · 07-09-2024, 06:07 AM

I do not need to add rows in every sheet. I've attached a new excel for reference. Only in this 3 sheets the rows should be added

The code that you provided, it inserts rows at the end of the last row.

**Sintek** · 07-09-2024, 06:13 AM

I give up...Post 6 does what you want...

**TMS** · 07-09-2024, 08:39 AM

@Sintek: your code adds a row at the bottom of each Table. The OP want to insert a new row at the top of each Table (line 1).

Option Explicit

Sub Test()

Dim ws As Worksheet
Dim tbl As ListObject

For Each ws In ThisWorkbook.Worksheets
    Set tbl = ws.ListObjects(1)
    tbl.ListRows.Add 1
Next ws

End Sub

**TMS** · 07-09-2024, 08:44 AM

@E5254730: I don't think your Month to Date Averages are including the newly inserted rows. Year to Date looks OK.

**Sintek** · 07-09-2024, 08:51 AM

Oops misunderstood...Tx Trevor...

**TMS** · 07-09-2024, 09:34 AM

No worries. I don't often play with Tables in VBA so a learn for me.

**Sintek** · 07-09-2024, 10:30 AM

Each day, I add a new row at row 5 and in A5 cell, yesterday's date is added

Edit to Post2 code and nor Post6 as previously referenced in error...

Sub J3v16()
Dim ws As Worksheet, Nxt As ListRow
For Each ws In Sheets
    With ws.ListObjects(1)
        Set Nxt = .ListRows.Add(1)
        Nxt.Range(1) = Date
    End With
Next ws
End Sub

**E5254730** · 07-10-2024, 06:59 AM

Yes, this did the trick.

But when it comes to date, it adds today's date, whereas i want to have previous date (excluding weekends). Today it will add Jul 9th

**TMS** · 07-10-2024, 07:03 AM

Change it to: Nxt.Range(1) = Date - 1

**E5254730** · 07-10-2024, 07:06 AM

@TMS - thanks - I just added the code. It works.

Much Appreciated.

**TMS** · 07-10-2024, 07:06 AM

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Loop through multiple sheets to add a row

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Re: Loop through multiple sheets to add a row

Thread Information

Users Browsing this Thread

Similar Threads

VBA Loop to combine multiple zip codes to multiple items from 2 sheets onto 1

Excel Formula to Loop through multiple sheets

Loop add value to multiple sheets

Loop and Clear Multiple Sheets

[SOLVED] Loop codes through multiple sheets (not all sheets in workbook)

Macro to loop through multiple sheets

Loop with AdvancedFilter Using Multiple Sheets

Bookmarks

Bookmarks

Posting Permissions