Copy sheet based on cell value, but if this value exits in sheets don't create it

MichaelFl

New Member
Joined
Sep 18, 2018
Messages
2
Hi,

I'm trying create a copy of the active sheet and name it based on a cell value ("H1" is text January) using a command button.
This works fine. But the problem i run into is that when the name already exist I get an error 1004. Then it creates a copy of the sheet name like "test (2)".
What I want is when this value already exist in the sheets, it should not make a copy an no error.
So only make a copy if the value not exists.

This is the code:


Private Sub CommandButton1_Click()
Dim ws As Worksheet


Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("H1").Value <> "" Then
ActiveSheet.Name = wh.Range("H1").Value


ActiveSheet.Shapes("CommandButton1").Delete

End
End If


wh.Activate




End Sub

Thanks in advance!
Michael
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi MichaelFl,

Welcome to MrExcel!!

Though you have declared a ws worksheet variable you don't use it in the code? Using Option Explicit as I have done with the following will prevent this:

Code:
Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim strSheetName As String
    
    Application.ScreenUpdating = False

    On Error Resume Next
        If Len(ActiveSheet.Range("H1")) > 0 Then
            Set ws = Sheets(CStr(ActiveSheet.Range("H1")))
            If Err.Number <> 0 Then
                strSheetName = CStr(ActiveSheet.Range("H1"))
                ActiveSheet.Copy After:=Worksheets(Sheets.Count)
                With Worksheets(Sheets.Count)
                    .Name = strSheetName
                    .Shapes("CommandButton1").Delete
                End With
            End If
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = True

End Sub

Though I'm not a fan of having separate tabs, the above should do the job for you.

Hope that helps,

Robert
 
Upvote 0
Hi Robert,

Thanks for your fast reply!
Yes it works for me!
I am new to VBA and trying to understand the codes by trying, reading and help from this forum.

This certainly will help me in my learning curve :)
Thanks!
Michael
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top