Link to home
Start Free TrialLog in
Avatar of bikeski
bikeskiFlag for United States of America

asked on

Copy worksheet without references or formulas

Hello Experts,

The following code copies a worksheet which works. However, I need to copy the worksheet with the formatting, graphs, etc but without any formulas or references to other worksheets.

Sub AddSheet()

    Dim lngSheets As Long
    Dim intCount As Integer
   
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
   
    For lngSheets = 1 To Sheets.Count
        If Left$(Sheets(lngSheets).Name, Len(Range("N1"))) = Range("N1") Then
            intCount = intCount + 1
        End If
    Next
    If intCount > 0 Then
        ActiveSheet.Name = Range("N1") & intCount
    Else
        ActiveSheet.Name = Range("N1")
    End If
       
End Sub
SOLUTION
Avatar of Flora Edwards
Flora Edwards
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Martin.

 that this exactly as I suggested. you did the shortcut using "With"

:-)

anyways, I am your pupil
Avatar of bikeski

ASKER

Thanks, that worked. I gave Martin more points as he provided the full code;)
not fair :)
Flora, my code did use With, but more importantly it showed the author where to add the new code. In any case if you really don't think the current split is fair I'm happy to split the points with you 50/50. Just Request attention.
Martin,
no worries, of course I was just messing/joking with you :-)  
points are not important, what is important is that author got his answer and everyone is happy, including me :-)

thank you for being so generous and so nice.