+ Reply to Thread
Results 1 to 2 of 2

Code imports new sheet instead of updating existing & overwrites existing data

Hybrid View

  1. #1
    Registered User
    Join Date
    08-13-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Code imports new sheet instead of updating existing & overwrites existing data

    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

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Code imports new sheet instead of updating existing & overwrites existing data

    James

    It's a bit difficult without having the actual sheet to test against but you might try something like this.

    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'm copying a range assuming that row 1 is a header row ...

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

    and pasting into a sheet with the same name as the source sheet at the firat available cell in column D.

    Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
    Hope this helps.
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1