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("OldSheet").Copy Sheets("ws").PasteSpecial Paste:=xlPasteValues End Sub