CODEWITHSUNDEEP
vbaexcel
StormsEdge
StormsEdge

Reputation: 885

How to create and name an Excel Sheet referencing a cell in the workbook

I am trying to round out a rather large macro by copy and pasting the newly parsed data into a newly created sheet that is named with the date, which I am storing in cell B2 in the data insert sheet. So put most simply, I want to create a new sheet and name it with the contents (a date) in cell B2 located in a different sheet.

ActiveSheet.Range("A3:Q" & lastRow).Select
Selection.Copy

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets(ActiveSheet.Name).Name = ActiveWorkbook.Sheets(Trade_Data_Insert).Range("B2")

Upvotes: 1

Views: 1044

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

Change your macro to

Dim newSht As Worksheet
ActiveSheet.Range("A3:Q" & lastRow).Select
Selection.Copy

Set newSht = ActiveWorkbook.Sheets.Add(, Worksheets(Worksheets.Count))
newSht.Name = ActiveWorkbook.Sheets(Trade_Data_Insert).Range("B2").Value

also... is Trade_Data_Insert a set variable? if its the name you need to put it in ""

Upvotes: 1

Related Questions