don't copy specific object

[ashani]

hi everyone,

I'd really appreciate if someone could put me in teh right direction. I'm using the following Macro to copy the sheet, however I don't want to copy specific Object name "Setday" into new sheet. However, at the moment with the following code - it's not copying any object but I only want that "Setday" not to be copied.

Please can someone help me.

Thank you

Dim wks As Worksheet
    Set wks = ActiveSheet
    Application.CopyObjectsWithCells = False
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wks.Range("a3").Value <> "" Then
    On Error Resume Next

 

[GWteB]

The below code copies the entire sheet including any object and removes the desired object afterwards.

    Dim oWs As Worksheet, oWsNew As Worksheet, oShp As Shape
    
    Set oWs = ActiveSheet
    oWs.Copy After:=Sheets(Sheets.Count)
    Set oWsNew = ActiveSheet
    For Each oShp In oWsNew.Shapes
        If StrComp(oShp.Name, "Setday", vbTextCompare) = 0 Then
            oShp.Delete
            Exit For
        End If
    Next

 

[ashani]

Thank you

 

[ashani]

Hi
is it possible to have a message box appears if the sheet name has already taken rather than a VBA error message ?
many thanks

 

[ashani]

Here is the code I'm currently using.

Sub Newday()
     
    Dim oWs As Worksheet, oWsNew As Worksheet, oShp As Shape
   
    Set oWs = ActiveSheet
    oWs.Copy After:=Sheets(Sheets.Count)
    Set oWsNew = ActiveSheet
    ActiveSheet.Unprotect Password:=“abc”
    For Each oShp In oWsNew.Shapes
        If StrComp(oShp.Name, "Setday", vbTextCompare) = 0 Then
            oShp.Delete
    oWsNew.Name = Format(Range("R1").Value, ("ddmm"))
    oWsNew.Range("A2:N43").Locked = True
    oWsNew.Protect Password:=“abc”
            Exit For
        End If
    Next
    
    Sheets("Main").Select

 

[GWteB]

Have been offline awhile until now, so a delayed response...
Try the below code. Please note the separate SheetExists function.

Sub Newday()
     
    Dim oWs As Worksheet, oWsNew As Worksheet, oShp As Shape, sShtName As String
   
    Set oWs = ActiveSheet
    oWs.Copy After:=Sheets(Sheets.Count)
    Set oWsNew = ActiveSheet
    
    With oWsNew
        
        sShtName = Format(.Range("R1").Value, ("ddmm"))
        
        If Not SheetExists(.Parent, sShtName) Then
        
            .Unprotect Password:="abc"
            .Name = sShtName
            .Range("A2:N43").Locked = True
            
            For Each oShp In .Shapes
                If StrComp(oShp.Name, "Setday", vbTextCompare) = 0 Then
                    oShp.Delete
                    Exit For
                End If
            Next
            .Protect Password:="abc"
            
        Else
            MsgBox "There is already a sheet with the name " & sShtName, vbExclamation
        
        End If
    End With
    Sheets("Main").Select
End Sub


Function SheetExists(ByVal argWb As Workbook, ByVal argShtName As String) As Boolean
    Dim oWs As Worksheet
    If Not argWb Is Nothing And Len(argShtName) > 0 Then
        For Each oWs In argWb.Worksheets
            If StrComp(oWs.Name, argShtName, vbTextCompare) = 0 Then
                SheetExists = True
                Exit For
            End If
        Next oWs
    End If
End Function

 

[ashani]

Thank you

 

[GWteB]

You are welcome.