CODEWITHSUNDEEP
vbaexcel
Bigotacon
Bigotacon

Reputation: 45

Excel Add Sheet or Overwrite if Exists

In excel I have a macro that converts all of columns of an active sheet into an new sheet called "MasterList"

My problem is when I rerun that macro I get an error saying "That name is already taken." Try a different one.

I need my macro to overwrite MaterList sheet if it already exists.

Here is my code:

Sub ToArrayAndBack()
Dim arr As Variant, lLoop1 As Long, lLoop2 As Long
Dim arr2 As Variant, lIndex As Long

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With


ReDim arr2(ActiveSheet.UsedRange.Cells.Count - ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Count)

arr = ActiveSheet.UsedRange.Value


For lLoop1 = LBound(arr, 1) To UBound(arr, 1)
    For lLoop2 = LBound(arr, 2) To UBound(arr, 2)
        If Len(Trim(arr(lLoop1, lLoop2))) > 0 Then
            arr2(lIndex) = arr(lLoop1, lLoop2)
            lIndex = lIndex + 1
        End If
    Next
Next

Sheets.Add.Name = "MasterList"

Range("A1").Resize(, lIndex + 1).Value = arr2

Range("A1").Resize(, lIndex + 1).Copy
Range("A2").Resize(lIndex + 1).PasteSpecial Transpose:=True
Rows(1).Delete

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With


End Sub

Upvotes: 1

Views: 9123

Answers (1)

Tarik
Tarik

Reputation: 11209

You can put the sheet creation between on error resume and on error goto 0. The other solution is to loop through the workbook sheets collection and check if a sheet with that name exists.

Solution 1:

On Error Resume Next
Sheets.Add.Name = "MasterList"
On Error GoTo 0

Solution 2:

Dim ws As Worksheet
Dim found As Boolean
found = False
For Each ws In ThisWorkbook.Sheets
    If ws.Name = "MasterList" Then
        found = True
        Exit For
    EndIf
Next
If Not found Then
    Sheets.Add.Name = "MasterList"
EndIf

To avoid relying on the fact that MasterList is active:

Set ws = ThisWorkbook.Sheets("MasterList")
With ws
     .Range("A1").Resize(, lIndex + 1).Value = arr2

     .Range("A1").Resize(, lIndex + 1).Copy
     .Range("A2").Resize(lIndex + 1).PasteSpecial Transpose:=True
     .Rows(1).Delete
End With

Upvotes: 1

Related Questions