bikeski
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).Na me, 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
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).Na
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
that this exactly as I suggested. you did the shortcut using "With"
:-)
anyways, I am your pupil