[µo°Ý] sheet1 & Sheet2 Äæ¦ì¤ñ¹ï¡A¦p¦óÅܦ¨¾ã¦æ¦C¤ñ¹ï°õ¦æ

°g½k¤p®Ñ¹­

Sheet1 & Sheet2 ªº ³æ¤@Äæ¦ì¥i¥H°µ¤ñ¹ï¡A¦pªG­n§â¾ã®æÄæ¦ì¶]§¹ªº°j°é¡A«ç»ò½sĶ·|¤ñ¸û¦n¡A·Q¯}ÀY¤¤...

code as below:

Sub «ö¶s1_Click()
Dim Number As String

If Worksheets(1).Range("B2").Value = Worksheets(2).Range("B2") Then
Number = Worksheets(2).Range("P2").Value
Select Case Number
    Case Is = "Passed"
        Worksheets(1).Range("P2").Value = "Passed"
        Worksheets(1).Range("Q2").Value = Sheets(2).Range("Q2").Value
        Worksheets(1).Range("V2").Value = Sheets(2).Range("V2").Value
    Case Is = "Failed"
        Worksheets(1).Range("P2").Value = "Failed"
        Worksheets(1).Range("Q2").Value = Sheets(2).Range("Q2").Value
        Worksheets(1).Range("V2").Value = Sheets(2).Range("V2").Value
    Case Is = "Blocked"
        Worksheets(1).Range("P2").Value = "Blocked"
        Worksheets(1).Range("Q2").Value = Sheets(2).Range("Q2").Value
        Worksheets(1).Range("V2").Value = Sheets(2).Range("V2").Value
    Case Is = "Exempted"
        Worksheets(1).Range("P2").Value = "Exempted"
        Worksheets(1).Range("Q2").Value = Sheets(2).Range("Q2").Value
        Worksheets(1).Range("V2").Value = Sheets(2).Range("V2").Value
    End Select


MsgBox "finished"

End If

End Sub

°g½k¤p®Ñ¹­

¦^´_ 1# °g½k¤p®Ñ¹­

¾É¤J For loop
¦ý¬ORange("B2")=>¸Ì­±ªº("B2")¥i¥H¥ÎÅܼƨú¥N¶Ü?§Q¥Îi¦b+1Åܦ¨¦Û°Ê¥[¦æ¦C¥h³B²z¶Ü?

For i = 2 To 5000
a = "P" & i
If Worksheets(1).Range("B2").Value = Worksheets(2).Range("B2") Then
    Worksheets(1).Range("B & i").Vaule Worksheets(2).Range("B & i") ??

Number = Worksheets(2).Range("P2").Value

°g½k¤p®Ñ¹­

¦^´_ 2# °g½k¤p®Ñ¹­

for loop ¤w¸Ñ¥X

For i = 2 To 2000
a = "P" & i
b = "Q" & i
c = "V" & i
d = "B" & i
If Worksheets(1).Range(d).Value = Worksheets(2).Range(d) Then

Number = Worksheets(2).Range(a).Value
Select Case Number
    Case Is = "Passed"
        Worksheets(1).Range(a).Value = "Passed"
        Worksheets(1).Range(b).Value = Sheets(2).Range(b).Value
        Worksheets(1).Range(c).Value = Sheets(2).Range(c).Value
    Case Is = "Failed"
        Worksheets(1).Range(a).Value = "Failed"
        Worksheets(1).Range(b).Value = Sheets(2).Range(b).Value
        Worksheets(1).Range(c).Value = Sheets(2).Range(c).Value
    Case Is = "Blocked"
        Worksheets(1).Range(a).Value = "Blocked"
        Worksheets(1).Range(b).Value = Sheets(2).Range(b).Value
        Worksheets(1).Range(c).Value = Sheets(2).Range(c).Value
    Case Is = "Exempted"
        Worksheets(1).Range(a).Value = "Exempted"
        Worksheets(1).Range(b).Value = Sheets(2).Range(b).Value
        Worksheets(1).Range(c).Value = Sheets(2).Range(c).Value
        
    End Select

°g½k¤p®Ñ¹­

½Ð°Ý¥[¤J¤F¸ê®Æ§¨«Ø¸m§PÂ_¦¡¡A¾É­P"°õ¦æ®É¶¡¿ù»~5"³o­Ó°ÝÃD¡A¬O­þÃä¬yµ{¥X¤Fª¬ªp©O?
If Len(Dir(sPath & "Result", vbDirectory)) = 0 Then
    MkDir sPath & "Result"
End If

§¹¾ãcode
b = True
Application.DisplayAlerts = False
Application.ScreenUpdating = False
sPath = "C:\Users\Desktop\daliy report  demo test_1\"
  sFile = Dir(sPath & "\*.xlsx")   
Do While sFile <> ""
Workbooks.Open sPath & "\" & sFile, UpdateLinks:=0
               
   If i <> 1 Then
   'If Len(Dir(sPath & "Result", vbDirectory)) = 0 Then
    MkDir sPath & "Result"
  ' End If
    Sheets(1).Copy
    ActiveWorkbook.SaveAs sPath & "Result" & "\" & Format(Date, "mmdd") & "Result.xlsx"
    i = 1   '²Ä¤@¦¸¶}±Ò
    Workbooks(sFile).Close
   
  Else
    Sheets(1).Copy After:=Workbooks(Format(Date, "mmdd") & "Result.xlsx").Sheets(1)
    Worksheets(2).Name = "Sheet2"
    Call compare
    Worksheets(2).Delete
    Workbooks(sFile).Close
   
  End If
    sFile = Dir()
  Loop
   
  Workbooks(Format(Date, "mmdd") & "Result.xlsx").Close SaveChanges:=True