问用于协调的VBA行

EN

Sub Sample_Trade_Recon()

Application.ScreenUpdating = False



' Tab 1 & Tab 2 Raw data pasted in. Do a prelim sort of the actual columns 
you want compare on the summary sheet'


Sheets("QT").Select
Range("A3:G300").Select
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("A3:A300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("B3:B300"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("C3:C300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("D3:D300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("E3:E300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("F3:F300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("QT").Sort.SortFields.Add Key:=Range("G3:G300"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("QT").Sort
    .SetRange Range("A3:G300")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Sheets("SSC").Select
Range("A3:G300").Select
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("A3:A300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("B3:B300"), 
_
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("C3:C300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("D3:D300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("E3:E300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("F3:F300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SSC").Sort.SortFields.Add Key:=Range("G3:G300"), 
_
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SSC").Sort
    .SetRange Range("A3:G300")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With





' Tab 1 Raw data columns you want compared '

Sheets("QT").Select
Range("A3:A300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("QT").Select
Range("B3:B300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("QT").Select
Range("C3:C300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "m/d/yyyy"

Sheets("QT").Select
Range("D3:D300").Select
Selection.Copy
Sheets("Recon").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "m/d/yyyy"

Sheets("QT").Select
Range("E3:E300").Select
Selection.Copy
Sheets("Recon").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("QT").Select
Range("F3:F300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("QT").Select
Range("G3:G300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues



' Tab 2 Raw data columns you want compared '

Sheets("SSC").Select
Range("A3:A300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("H4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("SSC").Select
Range("B3:B300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("SSC").Select
Range("C3:C300").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Recon").Select
Range("J4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "m/d/yyyy"

Sheets("SSC").Select
Range("D3:D300").Select
Selection.Copy
Sheets("Recon").Select
Range("K4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "m/d/yyyy"

Sheets("SSC").Select
Range("E3:E300").Select
Selection.Copy
Sheets("Recon").Select
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("SSC").Select
Range("F3:F300").Select
Selection.Copy
Sheets("Recon").Select
Range("M4").Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("SSC").Select
Range("G3:G300").Select
Selection.Copy
Sheets("Recon").Select
Range("N4").Select
Selection.PasteSpecial Paste:=xlPasteValues




'Dynamic Sorting based on column total lengths'


Dim lastA, lastB, shortCol, rw As Integer

'Determine short column so we know when to stop
lastA = WorksheetFunction.CountA(Range("A:A"))
lastB = WorksheetFunction.CountA(Range("H:H"))
      If lastA > lastB Then _
      shortCol = 2 Else shortCol = 1



'Set First Check Row
rw = 4

nxtChk:

'Check Column A against Column H, Row by Row
'Insert cell at non-matching data
If Cells(rw, 26) <> "Keep" And shortCol = 2 Then


      Cells(rw, 8).Insert shift:=xlDown
      Cells(rw, 9).Insert shift:=xlDown
      Cells(rw, 10).Insert shift:=xlDown
      Cells(rw, 11).Insert shift:=xlDown
      Cells(rw, 12).Insert shift:=xlDown
      Cells(rw, 13).Insert shift:=xlDown
      Cells(rw, 14).Insert shift:=xlDown

Refresh all formulas in difference column so excel knows where to insert 
the skip row (based on formulas)'
Range("O4:Z4").Select
Selection.AutoFill Destination:=Range("O4:Z300")


     Else


If Cells(rw, 26) <> "Keep" And shortCol = 1 Then


      Cells(rw, 1).Insert shift:=xlDown
      Cells(rw, 2).Insert shift:=xlDown
      Cells(rw, 3).Insert shift:=xlDown
      Cells(rw, 4).Insert shift:=xlDown
      Cells(rw, 5).Insert shift:=xlDown
      Cells(rw, 6).Insert shift:=xlDown
      Cells(rw, 7).Insert shift:=xlDown

'Refresh all formulas in difference column so excel knows where to insert 
the skip row (based on formulas)'
Range("O4:Z4").Select
Selection.AutoFill Destination:=Range("O4:Z300")

End If



'If there is nothing left to check in the skiprow column , we're done
If Cells(rw, 26) = " " Then Exit Sub
'If not, increment Row counter and loop
rw = rw + 1

GoTo nxtChk


End If





Range("O4:Z4").Select
Selection.AutoFill Destination:=Range("O4:Z300")



Application.ScreenUpdating = True




'Operations Team notices to review data presentation'

MsgBox ("Please insert comments into Column AA for all differences !")

MsgBox ("NOTE: If identifer data sorting is off slightly; then please fix by 
repasting the data grids on either side up/down a row as needed to realign 
properly. This often happens when Identifiers Repeat ")



End Sub