current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

5 Answers 5

up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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)

share|improve this answer
up vote 1 down vote

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
share|improve this answer
    
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 –  Glenn Jul 17 '13 at 12:37
    
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. –  Ripster Jul 17 '13 at 12:42
    
Thank you Ripster and Andy G. It worked :) –  Glenn Jul 17 '13 at 12:57
up vote 0 down vote

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.

share|improve this answer
up vote 2 down vote 
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.

share|improve this answer
    
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? –  Glenn Jul 17 '13 at 12:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.