CODEWITHSUNDEEP
excelvba
user6302747
user6302747

Reputation:

I am trying to copy and rename a sheet but I get subscript out of range error

I am trying to copy Sheet2 to Sheet3 and rename it to Result but I keep getting subscript out of range error (which happens because that sheet doesn't exist which probably means that the copying isn't happening).

I tried using Copy as shown in the code below but the subscript out of range error keeps coming up. Any help resolving this is really appreciated. Thanks!

    Set shtSheet1 = Worksheets("Sheet1")
    Set shtSheet2 = Worksheets("Sheet2")

    'trying to copy Sheet2 and create a Sheet3 tab
    shtSheet2.Copy After:=Sheets("Sheet3")
    Set shtSheet3 = Sheets(Sheets("Sheet3").Index + 1)
    shtSheet3.Name = "Result"

Upvotes: 1

Views: 133

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Sheet

After Sheet2

Sub NewSheetAfterSheet2()

  Dim shtSheet2 As Worksheet
  Dim shtSheet3 As Worksheet
  Set shtSheet2 = Sheets("Sheet2")

  shtSheet2.Copy After:=shtSheet2
  Set shtSheet3 = Sheets(shtSheet2.Index + 1)
  shtSheet3.Name = "Result"

End Sub

Last Position

Sub NewSheetLastPosition()

  Dim shtSheet2 As Worksheet
  Dim shtSheet3 As Worksheet
  Set shtSheet2 = Sheets("Sheet2")

  shtSheet2.Copy After:=Sheets(Sheets.Count)
  Set shtSheet3 = Sheets(Sheets.Count)
  shtSheet3.Name = "Result"

End Sub

Upvotes: 2

Related Questions