VBA erroe pasting b/w workbooks - run time error 1004

[jt_aus]

Hi Guys,

I was hoping you could help a run time 1004 error I am getting on the following code:

Sub Test()
    Dim intNumber As Integer
    Dim intCount As Integer
    Dim wkbBook As Workbook
    Dim varFile As Variant
    Dim strSheet(1) As String
    strSheet(0) = "Audit Data"
    strSheet(1) = "Summary Data"
    
    varFile = Application.GetOpenFilename("All files,*.xls", 1, "Select", , True)
    If Not IsArray(varFile) Then Exit Sub
    Application.ScreenUpdating = False
    For intCount = 1 To UBound(varFile)
        Set wkbBook = Workbooks.Open(varFile(intCount))
        For intNumber = 0 To 1
            If WorkSheetExists(wkbBook.Name, strSheet(intNumber)) Then
                wkbBook.Sheets(strSheet(intNumber)).Range(Cells(2, 1), Cells.SpecialCells(xlCellTypeLastCell)).Copy _
                Destination:=ThisWorkbook.Sheets(wkbBook.Sheets(strSheet(intNumber)).Name).Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
            End If
        Next intNumber
        wkbBook.Close False
    Next intCount

End Sub



Public Function WorkSheetExists(ByVal wkbTemp As String, ByVal strName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not Workbooks(wkbTemp).Worksheets(strName) Is Nothing
End Function

The section causing the error is:

wkbBook.Sheets(strSheet(intNumber)).Range(Cells(2, 1), Cells.SpecialCells(xlCellTypeLastCell)).Copy _
                Destination:=ThisWorkbook.Sheets(wkbBook.Sheets(strSheet(intNumber)).Name).Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)

I am using Excel 2003. This code collects audit data from a number of workbooks completed by various auditors in various locations. The code is supposed to paste the data from the relevant sheets in the separate books into the main book, but I get the error.

I have looked up Microsoft help on the matter and gone through posts on this forum but have had no luck. Is anyone able to shed any light for me?

Thanks heaps.

Cheers

James

 

[sijpie]

What is this supposed to do?

Range(Cells(2, 1), Cells.SpecialCells(xlCellTypeLastCell))