How to Copy & Paste a renamed sheet as values

Asked Jan 13 '22 at 23:06

Active Jan 14 '22 at 00:09

Viewed 123 times

My goal is to copy and paste over a sheet as values in VBA, and rename the sheet as cell 'B7' in my newly copy/pasted sheet.

So far I have managed to copy and paste the sheet with the name set as 'B7' with the following code:

Sub RenameSheets()
'Updateby Extendoffice
    Dim ws As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("B7").Value <> "" Then
    ActiveSheet.Name = wh.Range("B7").Value
    End If
    wh.Activate
End Sub

I am not sure how to copy and paste the new sheet over as values though, and it retains all the formulas from the sheet I'm copying from...

I appreciate any help.

Thank you.

edited Jan 13 '22 at 23:12

Ken White

123,280
14
225
444

asked Jan 13 '22 at 23:06

Michaelia

After the copy these 3 more lines: `ActiveSheet.Cells.Copy, ActiveSheet.Range("A1").PasteSpecial xlValues, Application.CutCopyMode = False` – Ricardo A Jan 13 '22 at 23:32
2

`ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value`. – BigBen Jan 14 '22 at 00:08
@BigBen thank you, this worked perfectly for me! – Michaelia Jan 14 '22 at 02:16

How to Copy & Paste a renamed sheet as values

0 Answers0