Run Time Error '9' - Copying first worksheet from specific excel files

[drolvihs]

Hi, I am very new at VBA and have been exploring it to help automate a form I need to compile everyweek. After deleting worksheets indexed 4 and above, the code should reach in the same directory as the activework book and open specific files as outlined by source1, source2, source3, and source4. From there it should copy the first sheet of the workbook, paste it in the activeworkbook, rename it,and close the import file.

I have been slowly working out the kinks but have hit a wall. I am on Excel 2013, Windows 8.

Here is the code, any help is appreciated, this is my first time trying VBA, so I am sure that I could have done things alot differently (easier).

Public Sub CommandButton1_Click()


Dim directory As String
Dim fileName As String
Dim sheet As Worksheet
Dim total As Integer
Dim source1 As String
Dim source2 As String
Dim source3 As String
Dim source4 As String
Dim i As Integer
Dim storeno As String
Dim sourceno As String


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Do While Worksheets.Count > 3
   Worksheets(4).Delete
Loop


i = 12
source1 = Worksheets("Fill Info").Range("D1").Value
source2 = Worksheets("Fill Info").Range("G1").Value
source3 = Worksheets("Fill Info").Range("J1").Value
source4 = Worksheets("Fill Info").Range("M1").Value
storeno = Worksheets("Start").Range("B1").Value
directory = ActiveWorkbook.Path


Do While i < 16


   sourceno = Worksheets("Start").Cells(i, "A")
   total = i - 8
   fileName = directory & "\" & storeno & sourceno & ".xlsx"
    
    Workbooks.Open (fileName)
    Workbooks(fileName).Worksheets(1).Copy_
    after = Workbooks(ActiveWorkbook.Name).Worksheets(4)
    ActiveWorkbook.Worksheets(4).Name = sourceno
    Workbooks(fileName).Close
    
    i = i + 1
    
Loop
        
End Sub

 

[JoeMo]

You didn't say what signifies you have "hit a wall", but looking at your code I'm wondering if this bit:

Workbooks.Open (fileName) 
    Workbooks(fileName).Worksheets(1).Copy_     
after = Workbooks(ActiveWorkbook.Name).Worksheets(4)
    ActiveWorkbook.Worksheets(4).Name = sourceno

1. The line break after Copy needs a space before the underscore.
2. after = should be after:=
3. When you open a workbook it becomes the Active workbook. ActiveWorkbook.Name is the same as filename.
I suspect that's not what you want.

If what you want is to place a copy of a sheet from the workbook you have just opened
into the workbook the macro is in, then change ActiveWorkBook to ThisWorkbook.
</pre>

 

[drolvihs]

Thanks, now at least the other spreadsheet is opening up. But now it stops and gives me the same error and when I go into debug, this is the section that is highlighted.

    Workbooks(fileName).Worksheets(1).Copy _
    after:=Workbooks(ThisWorkbook.Name).Worksheets(total)

I tried researching the run time error and alot of what the material doesn't make sense to me, that's what I meant by hitting a wall.

I appreciate your help Joe. I made the changes you recommended and it is giving me the same error, maybe I missed something in there?

 

[JoeMo]

Thanks, now at least the other spreadsheet is opening up. But now it stops and gives me the same error and when I go into debug, this is the section that is highlighted.

    Workbooks(fileName).Worksheets(1).Copy _
    after:=Workbooks(ThisWorkbook.Name).Worksheets(total)

I tried researching the run time error and alot of what the material doesn't make sense to me, that's what I meant by hitting a wall.

I appreciate your help Joe. I made the changes you recommended and it is giving me the same error, maybe I missed something in there?

Looks to me like you have a variable dimensioned as an integer named "total", but you don't establish its value anywhere in your code so VBA considers it to be 0. The offending line says put a copy of a worksheet after worksheet(total) ---> worksheet(0). Worksheet indices begin at 1 so an index of 0 will cause an error. You need to assign a non-zero value to total somewhere in your code before the offending line.