1

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")
pnuts
  • 58,317
  • 11
  • 87
  • 139
StormsEdge
  • 854
  • 2
  • 10
  • 35
  • Have you seen [this](http://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets)? – findwindow Nov 02 '15 at 21:00
  • Yes I have. That's a static name though and that's the part i'm having trouble eliminating. The name needs to change as each new sheet is created with consecutive runs of the macro to what ever date is stored in B2 at the time of execution. – StormsEdge Nov 02 '15 at 21:04
  • I don't think you understand it then XD You need to create an object then set the name. Edit: `Sheets(ActiveSheet.Name).Name` has to change but `ActiveWorkbook.Sheets(Trade_Data_Insert).Range("B2")` does not, except I will qualify activeworkbook with another set object. – findwindow Nov 02 '15 at 21:05
  • I'm not following it exactly. I have experience with Swift so VBA is new to me. Syntax is tripping me up. – StormsEdge Nov 02 '15 at 21:33
  • It's a tad long to explain it fully and I am not exactly an expert :/ Perhaps read through [this](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx). – findwindow Nov 02 '15 at 21:43

1 Answers1

1

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 ""

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • Trade_Data_Insert is the name of the sheet being used to parse all the data and where the name of the worksheet is stored. I am getting a Run-time error '9': Subscript out of range when I run that code ^^ I reference trade data insert earlier in my code and it doesn't complain. – StormsEdge Nov 02 '15 at 22:36
  • as already said: if your worksheed is named Trade_Data_Insert you need to put "" to make it work -> `...ok.Sheets("Trade_Data_Insert").Ran...`. pretty well shown [HERE](http://stackoverflow.com/questions/33479352/trying-to-reference-another-worksheet-in-active-workbook) – Dirk Reichel Nov 03 '15 at 00:55