Macro to clear particular rows and columns in multiple worksheets

I am having issues with a particular macro. It works correctly but it is also clearing the headers which I don't want it to do in the 'Current' tab as well as the 'Closed' tab. The button 'Restart Report' is where this macro is in place on the 'Table of Contents' tab. I want to keep the panes frozen the way they are as well. The macro should only clear all content from columns A through M (of the 'Current' and 'Closed' tabs) since there are formulas in columns N, O and P.
DSE-Carelog-Report.xlsm
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
your current code is:
Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Import", "Current", "Closed"
            With sht
                    .Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window

you need to check for data bafore clearing the sheet:

Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Current", "Closed" ' "Import" << removed
            With sht
                If .Cells(.Rows.Count, 1).End(xlUp) > 2 Then 'check for data on sheet
                    .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
                End If
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window


This should fix the issues you have.
AckeemKAuthor Commented:
I am testing it without any data on the sheet to see if it will clear the headers from A to M and it does sadly. I am trying to avoid that from occurring so the user does not have to replace it back. Is it due to the fact that there is no data in these tabs?
Rgonzo1971Commented:
HI,

pls try

Option Explicit
Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Import"
            With sht
                .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
            End With
        Case "Current", "Closed"
            With sht
                .Range(.Cells(2, 1), Cells(Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row), 1)).Resize(, 13).ClearContents
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window

Regards
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

SteveCost AccountantCommented:
Why not keep it really really simple:

Option Explicit
Sub Cleardata()
    Dim SheetName
    
    For Each SheetName In Split("Import|Current|Closed", "|")
        Sheets(SheetName).Range("A2:M" & Sheets(SheetName).Rows.Count).ClearContents
    Next SheetName
    
    Sheets("Table of Contents").Range("B3").Value = "<Customer> Carelog"
    
End Sub

Open in new window


This will clear A to M from rows 2 to the end of each sheet in the string split by pipe.
AckeemKAuthor Commented:
This macro above is not clearing the headers thankfully. However, it is not clearing the entire 'Import' tab which I need it do. When that sheet is not cleared and I import another file, it is shifted to the far right. If I can clear that whole sheet, that would work perfectly.
SteveCost AccountantCommented:
The following will clear the defined range in the two sheets and the full import sheet:

Option Explicit
Sub Cleardata()
    Dim SheetName
    
    For Each SheetName In Split("Current|Closed", "|")
        Sheets(SheetName).Range("A2:M" & Sheets(SheetName).Rows.Count).ClearContents
    Next SheetName
    
    Sheets("Import").Cells.ClearContents

    Sheets("Table of Contents").Range("B3").Value = "<Customer> Carelog"
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.