VBA error

[Sharkie21]

I have the code below.

It keeps erroring out and asking to go into debug mode. However, once I am in debug mode it runs fine. I'm guessing the computer is too slow or something that it tries to set the worksheet to fast? No idea...

For Each f1 In fc
If Right(f1.Name, 4) = ".xls" Then
Set wb = Workbooks.Open(f1.Path)
Set wksheet = wb.Worksheets("Summary - with turnover") ' error here

 

[wigi]

Can you post the other code too please?

Can you do this using the

 tags? It makes the code readable.

 

[Sharkie21]

Option Explicit

Sub CollectExcelSheets()
Dim f1, fc As Object 'filesystem objects for looping through selected folders files.
Dim fd As FileDialog
Dim s, wksheet As Worksheet
Dim w As Workbook
Dim wb As Workbook
Dim wbname As String
Dim currRow As Long
Dim i, j, k As Integer
Dim numfiles As Long
Dim initialpath As String

On Error Resume Next

currRow = 4
Set w = ActiveWorkbook
initialpath = w.Path & "\"


Set s = w.Sheets("Summary")

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.AllowMultiSelect = False
fd.InitialFileName = initialpath
fd.Title = "Select Excel File Folder"
fd.Show

Set fc = CreateObject("Scripting.FileSystemObject").GetFolder(fd.SelectedItems(1)).Files


For Each f1 In fc
    If Right(f1.Name, 4) = ".xls" Then
        On Error Resume Next
        Set wb = Workbooks.Open(f1.Path)
        'get data
        Set wksheet = wb.Worksheets("Summary - with turnover")
        s.Cells(currRow, 1).Value = wb.Worksheets("Test Records").Range("F2").Value
        s.Cells(currRow, 2).Value = wksheet.Cells(128, 3).Value
        s.Cells(currRow, 3).Value = wksheet.Cells(127, 3).Value
        s.Cells(currRow, 4).Value = wksheet.Cells(35, 12).Value
        
        'scenario Baseline
        For i = 1 To 5
            s.Cells(currRow, i + 4).Value = wksheet.Cells(66, i + 2).Value
        Next
        
        For i = 1 To 5
            s.Cells(currRow, i + 10).Value = wksheet.Cells(68, i + 2).Value
        Next
        
        For i = 2 To 5
            s.Cells(currRow, i + 16).Value = (wksheet.Cells(70, i + 2).Value - wksheet.Cells(70, i + 1).Value)
        Next
             s.Cells(currRow, 17).Value = (wksheet.Cells(70, 3).Value - wksheet.Cells(35, 12).Value)
        
        'scenario 1A
        For k = 1 To 5
            s.Cells(currRow, k + 22).Value = wksheet.Cells(73, k + 2).Value
        Next
        
        For k = 1 To 5
            s.Cells(currRow, k + 28).Value = wksheet.Cells(75, k + 2).Value
        Next
        
        For k = 2 To 5
            s.Cells(currRow, k + 34).Value = (wksheet.Cells(77, k + 2).Value - wksheet.Cells(77, k + 1).Value)
        Next
            s.Cells(currRow, 35).Value = (wksheet.Cells(77, 3).Value - wksheet.Cells(35, 12).Value)
        
        'get 2011 data
        s.Cells(currRow, 41).Value = wksheet.Cells(35, 6).Value
        s.Cells(currRow, 42).Value = wksheet.Cells(35, 9).Value
        s.Cells(currRow, 43).Value = wksheet.Cells(35, 12).Value - wksheet.Cells(35, 4).Value
        
        wb.Close (vbNo)
        Set wb = Nothing
        currRow = currRow + 1
    End If

Next


Set fc = Nothing
Set fd = Nothing
End Sub

It errors out at this line all the time

Set wksheet = wb.Worksheets("Summary - with turnover")

 

[Sharkie21]

fix was to hit ctrl + break when I wasn't running the code.

 

[wigi]

- are you really really really sure the sheet exists in all the files in the loop? No typos and so on?

- your declaration of f1 is not as Object, but as Variant. Similarly, i and j are not integers. Can you find out why?

I am not sure this will solve the problem but at least it's one step closer

 

[wigi]

fix was to hit ctrl + break when I wasn't running the code.

What do you mean? It's not normal to hit ctrl + break while executing code.
And why would you hit it when you are not running code?