Need to compare two Sheets using Macro

nareshmedarmatila

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
mole999 said:
@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
Click to expand...


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
474
Sean15
Sean15
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
191
Alex Blakenburg
A
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
359
MARK858
MARK858

Forum statistics

Threads
1,224,814
Messages
6,181,120
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top