what is the vba reference for newly added sheet

[kprabhupaul]

Dear All,

i have added a new sheet to my workbook by the following code.

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

now, what is the reference for that added sheet.

because I have to code some more with/in the newly added sheet.

thank you.

[kprabhupaul]

i'm using the below, but it's not working..

Sheets(""sheet"&worksheets.count + 1").Select

help me guys

[xlnitwit]

Hi,

The new sheet will be activated automatically so you can use ActiveSheet. You can also refer to it by position since you added it as the last sheet using Worksheets(Worksheets.Count).

[kprabhupaul]

getting subscript out of range error

[xlnitwit]

Using which code? You must not add 1 to Worksheets.Count.

[kprabhupaul]

Sub SR()
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
Sheets("stock").Select
Cells.Select
Selection.Copy
Worksheets("sheet" & Worksheets.Count).Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("B:B").EntireColumn.AutoFit
Rows("1:2").Select
Selection.delete Shift:=xlUp
Columns("C:F").Select
Selection.delete Shift:=xlToLeft
Columns("D:H").Select
Selection.delete Shift:=xlToLeft
Columns("A:A").Select
Selection.delete Shift:=xlToLeft
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("A51:B100").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("A101:B150").Select
Selection.Cut
Range("A51").Select
ActiveSheet.Paste
Range("A151:B200").Select
Selection.Cut
Range("D51").Select
ActiveSheet.Paste
Range("A201:B241").Select
Selection.Cut
Range("A101").Select
ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:E1").Merge
Range("a1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("A1") = "STOCK RECORD FROM " & Worksheets("STOCK").Range("C1").Value & " TO " & Worksheets("STOCK").Range("F1").Value
End Sub

-

please correct the code in line 6

[xlnitwit]

It is, as I posted, simply:

Please Login or Register  to view this content.

However it is preferable to avoid selecting objects when using VBA. To wit:

Please Login or Register  to view this content.

[kprabhupaul]

Thanks, it's working brother ..

[kprabhupaul]

And also thanks for your simplification of the code ..