Add sheet and rename sheet and paste only value

[Giroen]

Hello

I have found a code which allow me to rename the sheet according a cell value and it work perfectly.

Sub Copyrenameworksheet()
'Updateby Extendoffice

Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("I3").Value <> "" Then
ActiveSheet.Name = wh.Range("I3").Value


End If
wh.Activate

End Sub


The only problem i would like to have value instead formulas copy,

Is it possible to add some code within the one above.
could you please help me to found a solution.

Thanks a lot

[Logit]

Do not copy the sheet.

Add a blank sheet to our workbook.

Re the activesheet ... COPY and PASTE VALUES ONLY.

Do a search for PASTE VALUES ONLY. Plenty of examples on the internet and this forum as well as other forums.

[Giroen]

Hello Logit

Finally i have found the solution

Just in case you could use it.

Sub Copierenameworksheet11()
Dim wh As Worksheet, nomf As String, c As Range
Set wh = Sheets("recap")
nomf = wh.Range("b3").Value
wh.Copy after:=Worksheets(Sheets.Count)
If wh.Range("b3").Value <> "" Then
ActiveSheet.Name = nomf
For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
c.Copy
c.Select
Selection.PasteSpecial Paste:=xlPasteValues
Next c
End If
wh.Activate

End Sub

[Logit]

Good job !!




Option Explicit


Sub Copierenameworksheet11()
Dim wh As Worksheet, nomf As String, c As Range


Set wh = Sheets("recap")


nomf = wh.Range("b3").Value


wh.Copy after:=Worksheets(Sheets.Count)

If wh.Range("b3").Value <> "" Then

ActiveSheet.Name = nomf

For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
c.Copy
c.Select '<<----------------------------------------------------- What happens if you delete this line ?
Selection.PasteSpecial Paste:=xlPasteValues '<<------------------ Then change this line to :
'c.PasteSpecial Paste:=xlPasteValues
Next c

End If


wh.Activate


End Sub

[jolivanes]

Sub Oder_So_Etwas()
Dim nm As String
If ActiveSheet.Range("I3") <> "" Then
nm = ActiveSheet.Range("I3").Value
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
With ActiveSheet
.Name = nm
.UsedRange.Value = .UsedRange.Value
End With
End If
End Sub