Hi,

I am hoping to get some help with some VBA code I am attempting to use to import data from multiple Excel workbooks.

Background:
There are approx 6 Excel 2003 audit workbooks that all have 7 identical worksheets, although they contain varying amounts of audit data (no more than 400 records in each). I am wanting to import this data to a kind of master file which is set up for dashboards. The master file has the same 7 worksheets (plus other ones not related to this) which need to be updated.

As an example, I need to to transfer the data from worksheet "Audit Data" in workbook 1 through workbook 7, to worksheet "Audit Data" in the master file. As they will all have different data, I need it to not replace the data as it copies from each workbook, but to place it in the next available row.

In addition, I require the information to be imported starting at D2 in each worksheet because of other formulas in place in the workbooks. I can work around this though, if it is an issue.

After hunting around for a while, I found an existing piece of code on this forum which is great and is nearly perfect but I'm having trouble adapting it (aside from worksheet names etc).

Sub Test()
    Dim intNumber As Integer
    Dim intCount As Integer
    Dim wkbBook As Workbook
    Dim varFile As Variant
    Dim strSheet(7) As String
    strSheet(0) = "Audit Data"
    strSheet(1) = "Motor Vehicle Summary"
    strSheet(2) = "Motorcycle Summary"
    strSheet(3) = "Boat Summary"
    strSheet(4) = "Caravan Summary"
    strSheet(5) = "Travel Summary"
    strSheet(6) = "Home Summary"
    strSheet(7) = "Landlords Summary"
    On Error GoTo Fin
    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 7
            If WorkSheetExists(wkbBook.Name, strSheet(intNumber)) Then
                wkbBook.Sheets(strSheet(intNumber)).Copy _
                    Before:=ThisWorkbook.Worksheets(1)
            End If
        Next intNumber
        wkbBook.Close False
    Next intCount
Fin:
    Application.ScreenUpdating = True
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
I have read dozens of posts on this topic, but this code really best suits my needs and I am really hoping to get it working.

Problem:


If WorkSheetExists(wkbBook.Name, strSheet(intNumber)) Then
                wkbBook.Sheets(strSheet(intNumber)).Copy _
                    Before:=ThisWorkbook.Worksheets(1)
I think this is my issue. This creates new worksheets in the master file for each of the worksheets imported. Eg. "Audit Data (2)", "Audit Data 3" etc.

I have tried to correct this using my own VBA knowledge and by using other pieces of code I have found, but none seem compatible with the rest and my splicing causes errors. In its current form, the code runs smoothly without errors, so its not an error with the code itself, its just not suitable to my needs.

If anyone could help correct this bit of code for me, I would be incredibly thankful. I'm hoping I have provided the information you will need, but if not, please just let me know.

Thanks for offering a great service.

Regards,

James