Merge two worksheets in 1 WB with different column orders VBA

[85characters]

Hi,
I’m new here and I’ve been working on a macro that’s driving me crazy. So I have three worksheets in one workbook (A,B and C). I want to merge data from four columns across worksheets A and B onto worksheet C. However, worksheets A and B do not have my four columns listed in the same order. Moreover, I want to add an additional column from only worksheet A.

So, in one workbook, it should essentially it should look like:

[TABLE="width: 855"]
<tbody>[TR]
[TD]WORKSHEET A

Column Headers -
[TABLE="width: 178"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[/TR]
[TR]
[TD]Store
[/TD]
[/TR]
[TR]
[TD]Dept
[/TD]
[/TR]
[TR]
[TD]Cashier
[/TD]
[/TR]
[TR]
[TD]Manager
[/TD]
[/TR]
[TR]
[TD]State
[/TD]
[/TR]
[TR]
[TD]Amt
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]+
[/TD]
[TD]WORKSHEET B

Column Headers -
[TABLE="width: 128"]
<tbody>[TR]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD]Cashier
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]Customer Name
[/TD]
[/TR]
[TR]
[TD]Item Display Type
[/TD]
[/TR]
[TR]
[TD]Vendor
[/TD]
[/TR]
[TR]
[TD]Dept
[/TD]
[/TR]
[TR]
[TD]Store
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]=
[/TD]
[TD]WORKSHEET C

Column Headers -

[TABLE="width: 219"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[/TR]
[TR]
[TD]Store
[/TD]
[/TR]
[TR]
[TD]Dept
Cashier
[/TD]
[/TR]
[TR]
[TD]Amt
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]


But I can’t figure out how to write this macro in VBA and I cannot get it. Can anyone help?

Thank you,
85characters

 

[NeonRedSharpie]

Standard VBA questions:

Do you want to merge duplicate records or count them?
Will the columns always be in the same order or not?
The "amount" column is only in the first sheet, so the values will only be attributed to those in sheet 1, correct?

 

[85characters]

Standard VBA questions:

Do you want to merge duplicate records or count them?
Will the columns always be in the same order or not?
The "amount" column is only in the first sheet, so the values will only be attributed to those in sheet 1, correct?

Hi,

1) I would want to merge duplicate records
2) Yes, the columns will always be in the same order
3) Correct, the values will only be attributed to those in sheet 1

Hope that helps.

Thank you!

 

[NeonRedSharpie]

Sub mergeTwoWorksheets()

    Dim worksheetA As Worksheet
    Dim worksheetB As Worksheet
    Dim worksheetC As Worksheet
    
    Dim rowStart As Integer
    Dim rowEndA As Long
    Dim rowEndB
    
    Set worksheetA = ActiveWorkbook.Sheets("WorksheetA")
    Set worksheetB = ActiveWorkbook.Sheets("WorksheetB")
    
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.name = "WorksheetC"
    
    Set worksheetC = ActiveWorkbook.Sheets("WorksheetC")
    
    rowStart = 2
    rowEndA = worksheetA.Cells(Rows.Count, "A").End(xlUp).Row
    rowEndB = worksheetB.Cells(Rows.Count, "A").End(xlUp).Row
    
    worksheetA.Activate
    worksheetA.Range(Cells(1, 1), Cells(rowEndA, 4)).Copy
    worksheetC.Activate
    worksheetC.Cells(1, 1).Activate
    Selection.PasteSpecial
    
    worksheetA.Activate
    worksheetA.Range(Cells(1, 7), Cells(rowEndA, 7)).Copy
    worksheetC.Activate
    worksheetC.Cells(1, 5).Activate
    Selection.PasteSpecial
    
    
    ' This...I don't know why I can't get it work any other way
    worksheetB.Activate
    worksheetB.Range(Cells(rowStart, 3), Cells(rowEndB, 3)).Copy
    worksheetC.Activate
    worksheetC.Cells(rowEndA + 1, 1).Activate
    Selection.PasteSpecial
    
    worksheetB.Activate
    worksheetB.Range(Cells(rowStart, 7), Cells(rowEndB, 7)).Copy
    worksheetC.Activate
    worksheetC.Cells(rowEndA + 1, 2).Activate
    Selection.PasteSpecial
    
    worksheetB.Activate
    worksheetB.Range(Cells(rowStart, 6), Cells(rowEndB, 6)).Copy
    worksheetC.Activate
    worksheetC.Cells(rowEndA + 1, 3).Activate
    Selection.PasteSpecial
        
    worksheetB.Activate
    worksheetB.Range(Cells(rowStart, 1), Cells(rowEndB, 1)).Copy
    worksheetC.Activate
    worksheetC.Cells(rowEndA + 1, 4).Activate
    Selection.PasteSpecial
    
    worksheetC.Range(Cells(1, 1), Cells(rowEndA + rowEndB, 5)).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _
        Header:=xlYes
End Sub

I've been sitting here yelling at my computer because I swear this is not optimal, but it should do what you need. Common disclaimer: Make sure to save your worksheet before running. This works on my sample dataset but might cause issues on your actual data.

 

[85characters]

No, it WORKS! What do you mean that it isn't optimal? Is there a more effective way that I could be doing this besides VBA? I'm open to suggestions. Regardless, you are amazing and you've made my day and you're really just a boss. Thank you

 

[NeonRedSharpie]

I mean it's not optimal because if you look at my copy/paste code...it's ugly as sin. I couldn't get the workbooks to copy if they weren't active...which maybe isn't possible. I'm having a bad VBA day today, don't mind me. VBA is definitely the way to do it. And if it runs slowly, I also forgot to put the:

Application.ScreenUpdating = False
'CODE CODE CODE
Application.ScreenUpdating = True

in there. See? Bad VBA day. But I'm glad it works!