CODEWITHSUNDEEP
vb.netexcelworksheet-function
mrn
mrn

Reputation: 425

Why excel worksheet name has (2) in it?

I am using following VB.NET code to copy an excel sheet in a same workbook, but the sheet names are written with (2) every time, what is wrong here?

Dim inp as Integer
inp=Val(Textbox1.Text)

oWB = oXL.Workbooks.Open("D:\testfile.xlsx")
oSheet = oWB.Worksheets("base")
With oWB
   For i = 0 To inp - 1
     oSheet.Copy(Before:=.Worksheets(i + 1))
     With oSheet
       .Name = "INP" & i + 1
     End With
   Next
End With

How to get rid of "(2)" on the sheet name?

Thanks

Upvotes: 0

Views: 846

Answers (2)

Sorceri
Sorceri

Reputation: 8033

Just reference the worksheet you created and rename it. You just need the line Worksheets(i + 1).Name = "INP" & i + 1 see below

Dim inp as Integer
inp=Val(Textbox1.Text)

oWB = oXL.Workbooks.Open("D:\testfile.xlsx")
oSheet = oWB.Worksheets("base")
With oWB
   For i = 0 To inp - 1
     oSheet.Copy(Before:=.Worksheets(i + 1))
     'reference and name worksheet
     Worksheets(i + 1).Name = "INP" & i + 1
   Next
End With

Upvotes: 1

nullrevolution
nullrevolution

Reputation: 4137

the following line:

     oSheet.Copy(Before:=.Worksheets(i + 1))

is creating a copy of the sheet you specified. when you create a copy of a sheet, excel adds (2) to the name to prevent having duplicate sheet names.

lets say for example you had a sheet named base that you were trying to copy. after processing that line above, you'd end up with the original sheet named base and a new sheet named base (2).

then, the next few lines of your code:

     With oSheet
       .Name = "INP" & i + 1
     End With

just rename the original sheet.

try this instead of those two lines:

     With oWB.Worksheets("base (2)")
       .Name = "INP" & i + 1
     End With

Upvotes: 1

Related Questions