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?
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.

SteveCommented:
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.
0
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?
0
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

SteveCommented:
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.
0
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.
0
SteveCommented:
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

0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.