VBA excel code for copying sheets error

Question

I want a copier to copy one sheet multiple times. So I have made a week planning for week 1. And now I want this sheet 51 more times. So the name has to start with a 2 and end with a 52. To make this work for x To y I made the following code:

   Sub Copier()
   Dim a As Integer
   Dim b As Integer

   a = InputBox("Enter begin number for making copy's")
   b = InputBox("Enter end number for making copy's")

   For x = a To b
      'Loop to make x number copies.
      ActiveWorkbook.ActiveSheet.Copy _
         Before:=ActiveWorkbook.Sheets("x")
         'The name of every copied sheet is a number.
   Next
End Sub

When I execute this, it gives an error : "Error 9 during executing. The subscript is out of range." (I translated it because I have Dutch Excel.)

I don't see what is wrong because this code is copied from the Microsoft page. Has anyone an idea?

Andy G · Accepted Answer · 2013-07-17 12:16:54Z

2

Before:=ActiveWorkbook.Sheets("x")

this is looking for a sheet named "x". You may have intended Sheets(x), which refers to a worksheet by its Index number. However, this won't name the sheet - you don't have this code yet.

answered Jul 17, 2013 at 12:16

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Glenn Over a year ago

Thanks I did not understand that they meant the name of the sheet they want to copy. Do you know how i can get the index of the sheet with as input name of it?

LS_ᴅᴇᴠ · Accepted Answer · 2013-07-17 13:08:25Z

2

Replace Before:=ActiveWorkbook.Sheets("x") with Before:=ActiveWorkbook.Sheets(CStr(x))

Sheets("x") whould look for sheet named exactly "x"

Sheets(x), if x is not string type, whould look for sheet with index x

Sheets(CStr(x)), ensures your looking for sheet named x.

answered Jul 17, 2013 at 13:08

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Comments

Ripster · Accepted Answer · 2013-07-17 12:54:30Z

1

In your code you used "x" which is a string. If you want to use x as an index you need to use it without the quotation marks.

Edit

Using x as a sheet index:

ActiveWorkbook.ActiveSheet.Copy Before:=ActiveWorkbook.Sheets(x)

Using the name of a sheet to get its index:

ActiveWorkbook.ActiveSheet.Copy Before:=ActiveWorkbook.Sheets(Sheets("Sheet1").Index)

Using the number of sheets as the index:

ActiveWorkbook.ActiveSheet.Copy Before:=ActiveWorkbook.Sheets(Sheets.Count)

If you are trying to change the name of the sheet after you copy it you need to add code that does this.

Example:

ActiveWorkbook.ActiveSheet.Copy Before:=ActiveWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = x

edited Jul 17, 2013 at 12:54

answered Jul 17, 2013 at 12:15

Ripster

3,6152 gold badges21 silver badges28 bronze badges

2 Comments

Glenn Over a year ago

Ripster this does not work. Because Sheets(x) refers to the index of the sheet like Andy G said. These index has to be the index of the heet I want to copy. I thought more of Before:=ActiveWorkbook.Sheets(1) ActiveSheet.Name = "x" but thsi does not work

Ripster Over a year ago

What you are trying to accomplish is unclear. Andy G and I said the exact same thing.. I'll update my code to try and explain what I think you are asking but again, it is fairly unclear to me.

Glenn · Accepted Answer · 2013-07-17 12:53:21Z

0

What I trying to accomplish was this:

Sub Copier()
   Dim a As Integer
   Dim b As Integer
   Dim c As String

   c = InputBox("Name of Sheet")
   a = InputBox("Enter begin number for making copy's")
   b = InputBox("Enter end number for making copy's")


   For x = a To b
      'Loop to make x number copies.
      ActiveWorkbook.ActiveSheet.Copy _
         Before:=ActiveWorkbook.Sheets(c)


   Next
End Sub

The only thing i now still want is that the name of the copied sheet become's x.

answered Jul 17, 2013 at 12:53

Glenn

691 silver badge9 bronze badges

Comments

SeanC · Accepted Answer · 2013-07-17 12:56:27Z

0

This macro will copy sheet "1" to sheets "2" to whatever....

Option Explicit

Sub MakeExtraWeeks()
Dim q As Long
Dim i As Long

q = InputBox("How Many extra weeks?")

For i = 2 To q
    Worksheets("1").Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = CStr(i)
Next i
End Sub

I copy After:= the final sheet, so I know exactly where to find the sheet, so I can rename it in the next line (remember that the count of sheets will have increased by 1, so the .Name will rename the final sheet)

answered Jul 17, 2013 at 12:56

SeanC

16k5 gold badges48 silver badges67 bronze badges

Collectives™ on Stack Overflow

VBA excel code for copying sheets error

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related