Dynamically update for sheet name

[Danny54]

Good Afternoon

I have a vb statement in my excel macro that I need to dynamically change the workbook sheet just created as the macro loops.

the current statement is
[Sheet1!A1:J1].Resize(N).Value2 = S

after that i have a line that adds a new sheet to the workbook
Sheets.Add After:=Sheets(Sheets.Count)

Then loop back to the statement
[Sheet1!A1:J1].Resize(N).Value2 = S

how would I code to use the sheet just created?


Thanks

 

[johnnyL]

?
Sheets(Sheets.Count) instead of Sheet1

 

[Danny54]

?

workflow goes like this
[Sheet1!A1:J1].Resize(N).Value2 = S <- this statement dumps the array S into Sheet3
Sheets.Add After:=Sheets(Sheets.Count) <- I then create a new sheet
Erase S() <- erase the array
ReDim S(1 To Rows.Count, 9) <- ReDim the array
loop here <- loop thru code to reload the array again

Now I need to dump the new array data into the newly created worksheet which would be Sheet2

The original statement as show above
"[Sheet1!A1:J1].Resize(N).Value2 = S" to this
"[Sheet2!A1:J1].Resize(N).Value2 = S" then i loop again creating a new sheet Sheet3, change this statement again and loop.

Thanks

 

[johnnyL]

Another hint, when you add a sheet it becomes the active sheet.

 

[johnnyL]

Did I confuse you?

 

[StephenCrump]

At minimum, it sounds like you could use something like:

With Sheets.Add(After:=Sheets(Sheets.Count))
    .Name = "SomeNewName"
    .Range("A1:J1").Resize(N).Value2 = S
End With

But it sounds like you want to create multiple new sheets? Do you need to "reload" S, or will S be different for each sheet?

Perhaps you could provide more detail?

 

[Danny54]

No, johnnyL. Got pulled into something else for a moment. Yes, you helped

Thanks to all that helped.

 

[johnnyL]

Ok. I was just going to suggest:

Instead of:
Start Loop
Dump array to sheet
Add a new sheet
Erase array
Redim array
Load array
Loop back to beginning

Try:
Start Loop
Dump array to sheet
Redim array
Load array
Add a new sheet
Loop back to beginning

That way you can dump to the active sheet and not have to worry about what the name of it is