Clear Contents Prior to Pasting New Data in Excel

[shelby55]

Hello

I'm using Excel 2010.

I have a macro in my workbook that copies the contents of some of the worksheets into a single worksheet called Master_NewA. With the way the code is written right now, it will keep adding the same worksheets after each other so I want to include code to see if there is data so it will delete first and then run the code and if not then just run the code.

The current code is:

 Sub Consolidate_NewA()
'this assumes
' Master sheet for combining data from all other sheets
' HEADINGS in Master in Row 1 starting in column A
' Mapping sheet with NAMED RANGES courtesy Skip_TekTips
'  Source & MasterCOL
    Dim ws As Worksheet, r As Range, wsMSTR As Worksheet, lRow As Long
         
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "Master_NewA", "Mapping_NewA", "RawData_A", "RawDataA_Map", _
                "Values", "Template", "ReadMe_Reabstractors", "ReadMe_Clients", _
                "Rat_Val"
                Case Else
                    lRow = wsMSTR.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [SourceNewA]
                        wsMSTR.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
                    Next
            End Select
        End With
    Next
End Sub

Thanks very much.

 

[SkipVought]

hi,

1. Your procedure NEVER Sets wsMSTR! That would be a problem.

2. Master_NewA is one of many that has values assigned to the unassigned wsMSTR???

This code does not resemble your question???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

1. You are correct..it was in an earlier version of the code but missed.

2. Not sure what you're saying/asking/telling

3. It is relevant because I am assuming that it is within this code that I will be adding code saying if data in the worksheet then clear it, and run the code and if not just run the code.

Thanks.

 

[shelby55]

It's okay, figured it out. Thanks.

 

[SkipVought]

It would help other browsers to know how you "figured it out."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Okay. The code that I posted copies all from the various worksheets and collects into one worksheet. Each worksheet is assigned a number in sequential order 1", "2" etc. In order to run the macro there has to be these worksheets or there is nothing to extract so I figured I first needed to check if they were there, then clear the contents and paste. If not, then provide a message indicating that Abstract Data needed to be run first.

So the function to find worksheet name is:

Public Function worksheetexists(ByVal worksheetname As String) As Boolean
On Error Resume Next
worksheetexists = (Sheets(worksheetname).Name <> "")
On Error GoTo 0
End Function

The amended code from my original query is:

Sub Consolidate_NewA()
'this assumes
' Master sheet for combining data from all other sheets
' HEADINGS in Master in Row 1 starting in column A
' Mapping sheet with NAMED RANGES courtesy Skip_TekTips
'  Source & MasterCOL
    Dim WS As Worksheet, r As Range, wsMSTR As Worksheet, lRow As Long
    
    Set wsMSTR_A = Sheets("Master_NewA")
    
    If worksheetexists("1") Then
        
    With wsMSTR_A
    .Range("A2.XB1000").ClearContents
    End With
    
    For Each WS In Worksheets
        With WS
            Select Case .Name
                Case "Master_NewA", "Mapping_NewA", "RawData_A", "Mapping_NewB", "Master_NewB", "RawDataA_Map", _
                "Values", "Template", "Mapping_NewC", "Master_NewC"
                Case Else
                    lRow = wsMSTR_A.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [SourceNewA]
                        wsMSTR_A.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
                    Next
            End Select
        End With
    Next
    
With Sheets("Master_NewA")
.Activate
.Visible = xlSheetVisible
End With

Else
    MsgBox "Abstracts haven't been created yet to consolidate.  Run Abstract Data first."
    End If
End Sub