(Solved) – How to copy the entire sheet to newly created sheet referencing the sheet’s name in Excel?

  • by

I wanted to create a sheet and rename it from referencing a cell in another sheet. This worked so far.

However, I’d like to copy and paste everything as values but maintaining the format from the old sheet to this newly created sheet. I don’t know how to reference this just newly renamed sheet.

Code as follows:

Private Sub CreateSheet()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=Worksheets("OldSheet"))

'the new sheet has the name from B3 of the old sheet.
ws.Name = Sheets("OldSheet").Range("B3")

'this copy paste part doesn't work, it gives an error.

Sheets("ws").PasteSpecial Paste:=xlPasteValues

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *