How do I clear data from multiple named sheets through VBA?

Question

0.00/5 (No votes)

See more:

my workbook contains tons of data before get a fresh data from the connections i have to clear it from the existing data i got from the previous month. I have the VB code below but it will just work for a single sheet. what can i do to make it work for multiple sheets.

What I have tried:

Sub CleanTheTable()
Application.ScreenUpdating = False
Sheets("Active Roster").Select
ActiveSheet.ListObjects("ActiveRoster").HeaderRowRange.Select

If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If

With Worksheets("Active Roster").ListObjects("ActiveRoster")
.Range.AutoFilter
On Error Resume Next
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
ActiveWindow.SmallScroll Down:=-10000

End With
Application.ScreenUpdating = True
End Sub

Posted 17-May-16 23:06pm

Shilos_Dad

Updated 18-May-16 6:44am

Add a Solution

Comments

Richard MacCutchan 18-May-16 6:35am

Use a loop and the list of worksheets in the workbook, substituting each name in the list for each iteration of the loop.

Shilos_Dad 18-May-16 23:53pm

thank you.

ZurdoDev 18-May-16 11:46am

As Richard said, just look up how to loop. Simple. Or, you can hardcode all sheets names, bad idea, but can be done.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-18T06:44:00

Change your sub to be something like

VB.NET

Sub CleanTheTable(sheetNo As Integer)
    Application.ScreenUpdating = False
    Sheets(sheetNo).Select
    ActiveSheet.ListObjects("ActiveRoster").HeaderRowRange.Select
    
    If ActiveSheet.FilterMode Then
        Selection.AutoFilter
    End If
    
    Dim tbl As ListObject
    For Each tbl In Worksheets(sheetNo).ListObjects
        .Range.AutoFilter
        On Error Resume Next
        .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
        .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
        ActiveWindow.SmallScroll Down:=-10000
    Next
    Application.ScreenUpdating = True
End Sub

Then you can call it for all sheets similar to