Merge 2 Worksheets (Same concept as RegEdit Merge or Outlook Vcard)

Are those lines between the data separate cells or is each line in one cell? Also, do you plan on using this only for your own workbook and since we are at it what would the name of your workbook be? (Just to help get the code exactly the way you will need it.)

I've attached 2 workbooks to use as an example.

ClientList.xls is the workbook that holds my full client list
NewClients.xls would be the file sent to me from a colleague that I'm trying to merge with ClientList.xls

I tried to think of everything in the code, but it is possible that this may not always work.

What I did was put a button on your ClientList to import new clients from another workbook.

You can change where the file browser starts by changing the 1 to something like "C:/Documents and Settings/[user]/desktop"

i.e.
change

myNewFile = Application.GetOpenFilename("Excel Files" & _
        "(*.xls),*.xls", 1, "Find the new client list")

to

myNewFile = Application.GetOpenFilename("Excel Files" & _
        "(*.xls),*.xls", "C:/Documents and Settings/[user]/desktop", _
        "Find the new client list")

If you have any problems let me know,

I've attempted to put this into work on the spreadsheets but I'm have a bit of an issue.

Would you mind uploading the ClientList.xls file with the macro button directed toward NewClients.xls so I can see it in action, and reverse engineer from there.


I also think they're may be a more simple way to "Auto-Merge" that I haven't come across yet. Anyone have an idea with that?

I can't direct it directly to NewClients.xls because I do not know the path on your computer. Why don't you tell me exactly what doesn't seem to be working and we can go from there. Try stepping through the code.

Assume both files are located in C:/

Forgive me for asking some basic questions.

When in excel, I go to Macros->VBA Editor->New Module, and paste what you have written (replacing the file with C:/NewClients.xls)

I assigned the macro to a button in ClientList.xls, yet when its clicked nothing happens. It simply highlights the program you wrote.

Can you copy and paste the exact program from your Macro on VBA editor so I can confirm mine looks identical.

When you say "replacing the file with C:/NewClients.xls" do you mean you are replacing the 1 in that first part of the code with C:/NewClients.xls?

I may not have explained that well enough. You should just be replacing the 1 with the location of NewClients.xls, the 1 is automatically C:/ so if you use the code as is in your ClientList file it will automatically start you in the C:/ directory, I just suggested you replace the 1 with a folder closer if not exactly where you will be saving the NewClients.xls file.

Anyway here is the code I used

Option Explicit
Sub getmoreclients()
    Dim myNewFile As Variant
    Dim ws As Worksheet
    Dim firstrow As Integer
    
    Application.ScreenUpdating = False
    
    myNewFile = Application.GetOpenFilename("Excel Files" & _
        "(*.xls),*.xls", 1, "Find the new client list")
    If myNewFile = False Then
        MsgBox "You didn't select a file"
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Application.Workbooks.Open myNewFile
    For Each ws In Worksheets
        If ws.Range("A1") <> "" Then
            If ws.Range("A1") = "Client Name" Or ws.Range("A1").Font.Bold = True Then
                firstrow = 2
            Else
                firstrow = 1
            End If
            ws.Range("A" & firstrow & ":D" & Range("A65000").End(xlUp).Row).Copy
            ActiveWorkbook.Close
            Workbooks("ClientList.xls").Activate
            Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
            Application.CutCopyMode = False
            GoTo don
        Else
            GoTo nex
        End If
nex: Next
don:
Application.ScreenUpdating = True
End Sub