View Full Version : Add sheet and rename sheet and paste only value
Giroen
09-14-2021, 02:18 AM
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
09-14-2021, 08:59 AM
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
09-15-2021, 04:23 AM
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
09-15-2021, 09:16 AM
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
09-15-2021, 09:41 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.