• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to combine two worksheets into one using a unique Identifier

M

mgao77

New Member
Good Day Everyone,

Im looking for a clean VBA macro to look at two different sheets and combine them based on unique identifiers (staff IDs). I've recorded and cleaned a macro that does some copy pasting and then using vlookup to pull in the data (Code Provided below).

Im looking for a way to do this a little more efficiently as I will be looking at thousands of rows of data and Vlookups are quite bulky.

Does anyone have a better alternative to vlookups?


Code: 
Sub Combine()
'
' Combine Macro
'
 
'
    Sheets("Basic").Select
    Range("A1:AM365").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Sheets("Calibration").Select
    Range("B1:AM365").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Selection.End(xlToRight).Select
    Range("AN2").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("AN1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=1"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+2"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+1"
    Range("AO1").Select
    ActiveWindow.SmallScroll ToRight:=2
    Selection.AutoFill Destination:=Range("AO1:BH1"), Type:=xlFillDefault
    Range("AO1:BH1").Select
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R365C22,Sheet3!R[-2]C,FALSE)"
    Range("AN1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("AN3").Select
    Selection.Columns.AutoFit
    Selection.AutoFill Destination:=Range("AN3:AN366")
    Range("AN3:AN366").Select
    Range("AN4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R365C22,Sheet3!R[-2]C,FALSE)"
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R365C22,Sheet3!R1C40,FALSE)"
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R365C22,Sheet3!R[-2]C40,FALSE)"
    Range("AN3").Select
    Selection.AutoFill Destination:=Range("AN3:AN366")
    Range("AN3:AN366").Select
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R365C22,Sheet3!R1C,FALSE)"
    Range("AN3").Select
    Selection.AutoFill Destination:=Range("AN3:AN4"), Type:=xlFillDefault
    Range("AN3:AN4").Select
    Range("AN4").Select
    Selection.AutoFill Destination:=Range("AN4:AN366")
    Range("AN4:AN366").Select
    Range("AN3").Select
    Selection.Copy
    Range("AO3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("AO3:AO366")
    Range("AO3:AO366").Select
    Range("AO3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("AO3:BH3"), Type:=xlFillDefault
    Range("AO3:BH3").Select
    Selection.AutoFill Destination:=Range("AO3:BH365"), Type:=xlFillDefault
    Range("AO3:BH365").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub
 
​
Hi,​
​
Since Excel 2007, worst is using Select to copy / paste in a VBA code !​
​
You can work directly with range object just by reading the Range.Copy VBA help​
and its argument Destination …​
​
Another tip to speed up your process is desactivate the screen refresh, see property Application.ScreenUpdating …​
​
 
Thanks for the input Marc.
Surprisingly, I was able to use a code similar to the above to make it work quite effectively:

Code: 
Sub Combine()
'
' Combine Macro
'
 
'
    Sheets("Inputs -->").Select
    ActiveSheet.Next.Select
    Range("A1:AM7001").Select
    Selection.Copy
    Sheets("New Data").Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Sheets("Inputs -->").Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    Range("B1:AM7001").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("New Data").Select
    Selection.End(xlToRight).Select
    Range("AN2").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("AN1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=1"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+2"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+1"
    Range("AO1").Select
    ActiveWindow.SmallScroll ToRight:=2
    Selection.AutoFill Destination:=Range("AO1:BH1"), Type:=xlFillDefault
    Range("AO1:BH1").Select
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R7001C22,R[-2]C,FALSE)"
    Range("AN1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("AN3").Select
    Selection.Columns.AutoFit
    Selection.AutoFill Destination:=Range("AN3:AN7001")
    Range("AN3:AN7001").Select
    Range("AN4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R7001C22,R[-2]C,FALSE)"
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R7001C22,R1C40,FALSE)"
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R7001C22,R[-2]C40,FALSE)"
    Range("AN3").Select
    Selection.AutoFill Destination:=Range("AN3:AN7001")
    Range("AN3:AN7001").Select
    Range("AN3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,Calibration!R2C1:R7001C22,R1C,FALSE)"
    Range("AN3").Select
    Selection.AutoFill Destination:=Range("AN3:AN4"), Type:=xlFillDefault
    Range("AN3:AN4").Select
    Range("AN4").Select
    Selection.AutoFill Destination:=Range("AN4:AN7001")
    Range("AN4:AN7001").Select
    Range("AN3").Select
    Selection.Copy
    Range("AO3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("AO3:AO7001")
    Range("AO3:AO7001").Select
    Range("AO3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("AO3:BH3"), Type:=xlFillDefault
    Range("AO3:BH3").Select
    Selection.AutoFill Destination:=Range("AO3:BH365"), Type:=xlFillDefault
    Range("AO3:BH7001").Select
    Range("A2").Select
    Range("A1:bm7001").Select
    Selection.Columns.AutoFit
    Range("A1:bm7001").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.EntireRow.Select
        Selection.Delete Shift:=xlUp
    Range("A1").Select
End Sub
 
You must log in or register to reply here.
Back
Top