Need to compare two Sheets using Macro

nareshmedarmatila

New Member
Joined
Apr 1, 2020
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello All,

I need a big favor regarding a work


I need to compare sheet1 which consists old data and Sheet2 Which consists Some new data or edited one

I need to compare two sheets and need to highlight or need to cross check which is same and Different.

I don't know how to wirte vba code so I started recording macros and combined few code.

Please help me out how to reduce the code which is there below.
VBA Code:
Sub Match_MisMatch()
'
' Macro to find Wrong and Right Entry

 result = MsgBox("Are You Sure want to run this Match and MisMatch", vbOKCancel + vbQuestion, QC_TOOL)
 If result = vbCancel Then
 Exit Sub
 Else
 Sheets("Sheet2").Select
    Range("A:A,B:B,C:C").Select
    Range("C1").Activate
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("A:A").Select
    ActiveSheet.Paste
' Reference no
    Sheets("Sheet1").Select
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("D:D").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("E:E").Select
    ActiveSheet.Paste
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("F2").Select
    Range("E2:E" & Cells(Rows.Count, "E").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"   

' Invocie no
    Sheets("Sheet1").Select
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("G:G").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("H:H").Select
    ActiveSheet.Paste
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("I2").Select
    Range("H2:H" & Cells(Rows.Count, "H").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
    
   ' Couriner Name
    Sheets("Sheet1").Select
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("J:J").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("K:K").Select
    ActiveSheet.Paste
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("L2").Select
    Range("K2:K" & Cells(Rows.Count, "K").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
      
    ' Couriner No
    Sheets("Sheet1").Select
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("M:M").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("N:N").Select
    ActiveSheet.Paste
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("O2").Select
    Range("N2:N" & Cells(Rows.Count, "N").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
        
    ' Dispatch Date
    Sheets("Sheet1").Select
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("P:P").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("Q:Q").Select
    ActiveSheet.Paste
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("R2").Select
    Range("Q2:Q" & Cells(Rows.Count, "Q").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
            
    ' Dispatch By
    Sheets("Sheet1").Select
    Columns("I:I").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("S:S").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("I:I").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("T:T").Select
    ActiveSheet.Paste
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("U2").Select
    Range("T2:T" & Cells(Rows.Count, "T").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                
    ' Sales Date
    Sheets("Sheet1").Select
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("V:V").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("W:W").Select
    ActiveSheet.Paste
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("X2").Select
    Range("W2:W" & Cells(Rows.Count, "W").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                            
    ' Sales Time
    Sheets("Sheet1").Select
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("Y:Y").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("Z:Z").Select
    ActiveSheet.Paste
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AA2").Select
    Range("Z2:Z" & Cells(Rows.Count, "Z").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                
    ' Customer Name
    Sheets("Sheet1").Select
    Columns("L:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AB:AB").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("L:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AC:AC").Select
    ActiveSheet.Paste
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AD2").Select
    Range("AC2:AC" & Cells(Rows.Count, "AC").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                            
    ' Mail Address
    Sheets("Sheet1").Select
    Columns("M:M").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AE:AE").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("M:M").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AF:AF").Select
    ActiveSheet.Paste
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AG2").Select
    Range("AF2:AF" & Cells(Rows.Count, "AF").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                                                           
    ' Agent name
    Sheets("Sheet1").Select
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AH:AH").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AI:AI").Select
    ActiveSheet.Paste
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AJ2").Select
    Range("AI2:AI" & Cells(Rows.Count, "AI").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                              
    ' Address
    Sheets("Sheet1").Select
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AK:AK").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AL:AL").Select
    ActiveSheet.Paste
    Range("AM1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AM2").Select
    Range("AL2:AL" & Cells(Rows.Count, "AL").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                      
    ' City
    Sheets("Sheet1").Select
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AN:AN").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AO:AO").Select
    ActiveSheet.Paste
    Range("AP1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AP2").Select
    Range("AO2:AO" & Cells(Rows.Count, "AO").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                                               
    ' State
    Sheets("Sheet1").Select
    Columns("Q:Q").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AQ:AQ").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("Q:Q").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AR:AR").Select
    ActiveSheet.Paste
    Range("AS1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AS2").Select
    Range("AR2:AR" & Cells(Rows.Count, "AR").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                              
    ' Zip
    Sheets("Sheet1").Select
    Columns("R:R").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AT:AT").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("R:R").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AU:AU").Select
    ActiveSheet.Paste
    Range("AV1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AV2").Select
    Range("AU2:AU" & Cells(Rows.Count, "AU").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                           
    ' Customer Phone
    Sheets("Sheet1").Select
    Columns("S:S").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AW:AW").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("S:S").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AX:AX").Select
    ActiveSheet.Paste
    Range("AY1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("AY2").Select
    Range("AX2:AX" & Cells(Rows.Count, "AX").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                                                                               
    ' Credit Card Type
    Sheets("Sheet1").Select
    Columns("T:T").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("AZ:AZ").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("T:T").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("BA:BA").Select
    ActiveSheet.Paste
    Range("BB1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("BB2").Select
    Range("BA2:BA" & Cells(Rows.Count, "BA").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                                                                                                 
    ' Credit Card No
    Sheets("Sheet1").Select
    Columns("U:U").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("BC:BC").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Columns("U:U").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("BD:BD").Select
    ActiveSheet.Paste
    Range("BE1").Select
    ActiveCell.FormulaR1C1 = "Result"
    Range("BE2").Select
    Range("BD2:BD" & Cells(Rows.Count, "BD").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
                                                                                                                                                                                                                
    Rows("1:1").Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
 
' Need a table which need to show How many match and MisMatchs are there in Desired columns
 
Range("BG1").Select
ActiveCell.FormulaR1C1 = "ERRORS AFTER SUBMITTING FOR QC"
Range("BG2").Select
ActiveCell.FormulaR1C1 = "Name"
Range("BH2").Select
ActiveCell.FormulaR1C1 = "Match"
Range("BI2").Select
ActiveCell.FormulaR1C1 = "MisMatch"
Range("BG3").Select
ActiveCell.FormulaR1C1 = "ref_no"
Range("BG4").Select
ActiveCell.FormulaR1C1 = "invoice_no"
Range("BG5").Select
ActiveCell.FormulaR1C1 = "courier_name"
Range("BG6").Select
ActiveCell.FormulaR1C1 = "con_no"
Range("BG7").Select
ActiveCell.FormulaR1C1 = "dispatch_date"
Range("BG8").Select
ActiveCell.FormulaR1C1 = "dispatch_by"
Range("BG9").Select
ActiveCell.FormulaR1C1 = "sales_date"
Range("BG10").Select
ActiveCell.FormulaR1C1 = "sales_time"
Range("BG11").Select
ActiveCell.FormulaR1C1 = "customer_name"
Range("BG12").Select
ActiveCell.FormulaR1C1 = "mail_address"
Range("BG13").Select
ActiveCell.FormulaR1C1 = "agent_name"
Range("BG14").Select
ActiveCell.FormulaR1C1 = "address"
Range("BG15").Select
ActiveCell.FormulaR1C1 = "city"
Range("BG16").Select
ActiveCell.FormulaR1C1 = "state"
Range("BG17").Select
ActiveCell.FormulaR1C1 = "zip"
Range("BG18").Select
ActiveCell.FormulaR1C1 = "customer_phone"
Range("BG19").Select
ActiveCell.FormulaR1C1 = "creditcard_type"
Range("BG20").Select
ActiveCell.FormulaR1C1 = "creditcard_no"
Range("BG21").Select
ActiveCell.FormulaR1C1 = "Total"
Range("BH3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-56]:C[-54],R[-1]C)"
    Range("BH4").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-53]:C[-51],R[-2]C)"
    Range("BH5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-50]:C[-48],R[-3]C)"
    Range("BH6").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-47]:C[-45],R[-4]C)"
    Range("BH7").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-44]:C[-42],R[-5]C)"
    Range("BH8").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-41]:C[-39],R[-6]C)"
    Range("BH9").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-38]:C[-36],R[-7]C)"
    Range("BH10").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-35]:C[-33],R[-8]C)"
    Range("BH11").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-32]:C[-30],R[-9]C)"
    Range("BH12").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-29]:C[-27],R[-10]C)"
    Range("BH13").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-26]:C[-24],R[-11]C)"
    Range("BH14").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-23]:C[-21],R[-12]C)"
    Range("BH15").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-20]:C[-18],R[-13]C)"
    Range("BH16").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-17]:C[-15],R[-14]C)"
    Range("BH17").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-14]:C[-12],R[-15]C)"
    Range("BH18").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-11]:C[-9],R[-16]C)"
    Range("BH19").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-8]:C[-6],R[-17]C)"
    Range("BH20").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-5]:C[-3],R[-18]C)"
    Range("BH21").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    Range("BH3:BH21").Select
    Selection.AutoFill Destination:=Range("BH3:BI21"), Type:=xlFillDefault
    Range("BH3:BI21").Select
    Range("BG1:BI1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("BG2:BI2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("BG3:BG20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("BG21").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BH3:BI20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("BH21").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BI21").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Range("BF1:BJ1").Select
    Selection.AutoFilter
    Columns("BG:BI").EntireColumn.AutoFit
    Columns("BG:BI").EntireColumn.AutoFit
    Columns("BG:BI").EntireColumn.AutoFit
    Range("BG1:BI21").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("BG3").Select    
End If
End If
End Sub


Please do let me know if you required any Source data
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
selecting cause the system to do unnecessary tasks, to make it more readable you could improve the code once recorded

so
VBA Code:
 Sheets("Sheet2").Select
    Range("A:A,B:B,C:C").Select
    Range("C1").Activate
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("A:A").Select
    ActiveSheet.Paste

can be replaced with
VBA Code:
Sheet2.Range("A1:C10000").Copy Destination:=Sheet3.Range("A1")

similarly
Range("BG1").Select
ActiveCell.FormulaR1C1 = "ERRORS AFTER SUBMITTING FOR QC"


is better served as
Range("BG1") = "ERRORS AFTER SUBMITTING FOR QC"
as its not a formula but only text
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
The following macro does the same thing as your macro, I just put a loop.
Do not delete the format of the range BG1 to BI21 from sheet3.
Run the macro and all the results will be populated.

VBA Code:
Sub Match_MisMatch()
  Dim i As Long, j As Long, nMatch As Variant, nMissm As Variant
  Application.ScreenUpdating = False
  With Sheets("Sheet3")
    .Cells.ClearContents
    'PUT labels
    .Range("BG1").Value = "ERRORS AFTER SUBMITTING FOR QC"
    .Range("BG2:BI2").Value = Array("Name", "Match", "MisMatch")
    .Range("BG21").Value = "Total"
    .Range("BH21:BI21").Formula = "=sum(BH3:BH20)"
    Sheets("Sheet2").Range("A:A,B:B,C:C").Copy .Range("A1")
    j = 4
    For i = Columns("D").Column To Columns("U").Column
      'COPY columns
      Sheets("Sheet1").Columns(i).Copy .Columns(j)
      Sheets("Sheet2").Columns(i).Copy .Columns(j + 1)
      'PUT formula
      With .Range(.Cells(2, j + 2), .Cells(.Cells(Rows.Count, j + 1).End(3).Row, j + 2))
        .Formula = "=IF(RC[-2]=RC[-1],""Match"",""MisMatch"")"
        .Value = .Value
        nMatch = WorksheetFunction.CountIf(Range(.Address(external:=True)), "Match")
        nMissm = WorksheetFunction.CountIf(Range(.Address(external:=True)), "MisMatch")
      End With
      'PUT headers
      .Range("BG" & i - 1).Value = Sheets("Sheet1").Cells(1, i)
      'PUT counters
      .Range("BH" & i - 1).Value = nMatch
      .Range("BI" & i - 1).Value = nMissm
      j = j + 3
    Next
  End With
  Application.ScreenUpdating = True
End Sub
 

nareshmedarmatila

New Member
Joined
Apr 1, 2020
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello DanteAmor,

Its working like a charm!

OMG! you reduced the code. Its look Awesome.
I'm thinking like its is a magic!

Really Really Thanks for your time!

thank-you.jpg
 

nareshmedarmatila

New Member
Joined
Apr 1, 2020
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

I need another Help If you don't mind!


Is there any chance that I can add Processing Bar while I run Macro?

As shown in the Below Image!

It should come when I run a macro!

Is it Possible?




hqdefault.jpg
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
@nareshmedarmatila being this is a totally different and new question it should be in a new forum entry, this prevents them being a "personal" answer session for a single user. Your question has been asked before by others so there are guides on the forums of how to accomplish this in some form. I would suspect your macro now will run so much faster it won't be onscreen for long
 

nareshmedarmatila

New Member
Joined
Apr 1, 2020
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
@nareshmedarmatila being this is a totally different and new question it should be in a new forum entry, this prevents them being a "personal" answer session for a single user. Your question has been asked before by others so there are guides on the forums of how to accomplish this in some form. I would suspect your macro now will run so much faster it won't be onscreen for long


Thanks. I will post it as an New Thread beacuse it is a unique question!

Im asking process bar for other macros which im using constantly. I need to run more than 18 macros

I m planning to combine all in one and make it as a QC tool for my QC process
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top