Refering to a Sheet that has been Named using InputBox

[Newbie74]

Hi,

I am stuck on a piece of code that I can't figure out. If I have just inserted and named a new sheet using VBA how can I refer to this new sheet further down in my code? So far I have this...

Sub VlookupDivStocks()




Sheets.Add.Name = InputBox("What would you like to Call this sheet?")




Sheets("2013").Activate
Range("A1").EntireRow.Copy

Sheets(".........").activate <<<<<<what should="" go="" here="" to="" refer="" the="" new="" worksheet="" that="" was="" added="" earlier?
what should go here to refer to the newly inserted sheet?
range("a1").paste






End Sub</what>

 

[Jonmo1]

Seperate the line into 2 lines, and assign the result of the inputbox to a variable...

MyVaraible = InputBox(....)
Sheets.Add.Name = MyVariable

Sheets(MyVariable).Activate

 

[Teeroy]

In general, when you add a sheet it becomes the ActiveSheet. You can take advantage of that to get a reference to it. e.g.

Sheets.Add.Name = InputBox("What would you like to Call this sheet?")
Set NewSht = ActiveSheet

In this case though, I'd use Jonmo1's method.

 

[mikerickson]

Or

Dim newSheet as Worksheet
Dim newName as String

newName = InputBox("Name please")

Set newSheet = ThisWorkbook.WorkSheets.Add

newSheet.Name = newName

newSheet.Activate

 

[Newbie74]

Hi Jonmo1,

thanks for that, I was trying to do something similar, but I got my syntax mixed up!

 

[Newbie74]

thanks Mike, that's very useful too!

 

[Newbie74]

Thanks teeroy!

 

[Jonmo1]

Glad to help, thanks for the feedback.