ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸ê®Æ½Æ»s¶K¤W°ÝÃD¡C

[µo°Ý] ¸ê®Æ½Æ»s¶K¤W°ÝÃD¡C

¥»©«³Ì«á¥Ñ stephenlee ©ó 2020-7-18 12:55 ½s¿è

§Ú¦³ 2­Ó¤u§@ªí,¤@­Ó¬O¸ê®Æ¨Ó·½, ¥t¤@­Ó¬O­n±q ¸ê®Æ¨Ó·½¤¤,
±N¸ê®Æ½Æ»s¹L¥hªº¤u§@ªí¡C

¨Ò¦p¦b100Ä椤½Æ»s¬ù10Äæ, ¦Ó¨C¤@¦¸ §Ú³£¬O¦Û¤v¨C¤@¦æ ±q ²Ä¤G¦C½Æ»s¦Ü§À,¦A¨C¤@¦æ¶K¤W¦Ü ¥Ø¼Ð¤u§@ªí,³o¼Ë¤U¨Ó·|«Üªá®É¶¡¡C

¦P®É¦]¬° ¸ê®Æ¨Ó·½ªºÄæ¦ì±Æ¦C¥¼¥²¬O¨C¦¸¤@¼Ë, ¤µ¤Ñ¦b AÄ檺¸ê®Æ,©ú¤Ñ¥i¯à¦bCÄæ, ¦ý¬O Äæ¦ìªº¦WºÙ¬O©T©w¤£Åܪº¡C

§Ú·Q¥ÎVBA ¥ý´M§ä ¸ê®Æ¨Ó·½ªº©T©wÄæ¦ì¦WºÙ, ¦A¥Ñ¸ÓÄæ¦ìªº²Ä¤G¦C ¥´ª½½Æ»s¦Ü¥Ø¼Ð¤u§@ªíªº©T©wÄæ¦ìªº²Ä¤G¦Ü§À¦C¡C

¨Ò¦p¤u§@ªí1,¦³«Ü¦h¸ê®Æ, ¦ý§Ú¥u»Ý 4­ÓÄæ¦ì¦WºÙªº¸ê®Æ, ±N¸ê®Æ½Æ»s¦Ü¤u§@ªí2 ¤º¡C

·N«ä¬O§ä´M ¸ê®Æ¨Ó·½ªºÄæ¦ì, ¦A°t¥Ø¼Ð¶K¤W¤u§@ªí¤WªºÄæ¦ì¡C

¦]¬°ÁÙ¦³¨ä¥L¤u§@ªí¬O¶W©ó100Ä檺, ¦P®É­n©â¨úªº¸ê®Æ,¤]«Ü¦h, ¦p¦ó¥ÎVBA °µ¥H¤W°Ê§@,ÁÂÁ¡C


¸ê®Æ¨Ó·½ªº¬¡­¶Ã¯¦WºÙ ¤£¬O©T©wªº, ¦Ó¬¡­¶Ã¯ªº¤u§@ªí¦W¬° Raw Data ¬O©T©wªº

¶K¨ì ¥t¥~¤@­Ó¬¡­¶Ã¯¦W¬°Data.xlsm¤ºªº ¦WºÙ¤u§@ªí1 ¤º


question 2.zip (9.23 KB)

¸ê®Æ¨Ó·½ÀÉÀɦW¤£©T©w, ¶·¤â°Ê¶}±Ò¦A°õ¦æµ{¦¡:
Sub ½Æ»s()
Dim xB As Workbook, xU As Range, R&
Dim Sht As Worksheet, A, C%, xF As Range
Set Sht = Sheets("¤u§@ªí1")
Sht.UsedRange.Offset(1, 0).EntireRow.Delete
'-------------------------------------
For Each xB In Workbooks
    On Error Resume Next
    If xB.Name = ThisWorkbook.Name Then GoTo 101
    Set xU = xB.Sheets("Raw Data").UsedRange
    R = xU.Rows.Count - 1
    If Not xU Is Nothing Then Exit For
101: Next
On Error GoTo 0
If xU Is Nothing Then MsgBox "¨Ó·½ÀÉ®×¥¼¶}±Ò!  ": Exit Sub
If R = 0 Then MsgBox "¨Ó·½ÀÉ®×µL¸ê®Æ!  ": Exit Sub
'Äæ¦ì::¼Æ¶q-¸¹½X-¦WºÙ-¤é´Á
For Each A In Array("Total Qty", "no", "name", "date2")
    C = C + 1
    Set xF = xU.Rows(1).Find(A, Lookat:=xlWhole)
    If Not xF Is Nothing Then xF(2).Resize(R).Copy Sht.Cells(2, C)
Next
End Sub

XX001.rar (20.42 KB)


================================

TOP

¸ê®Æ¨Ó·½ÀÉÀɦW¤£©T©w, ¶·¤â°Ê¶}±Ò¦A°õ¦æµ{¦¡:
Sub ½Æ»s()
Dim xB As Workbook, xU As Range, R&
Dim Sh ...
­ã´£³¡ªL µoªí©ó 2020-7-19 11:29



  ­ã¤j,ÁÂÁÂÀ°¦£¡C

§Ú·Q½Ð°Ý¤@¤U¦pªG¬O¦b¨ä¥LWorkbooks¤ºªºWorksheet("Raw Data")¤º ½Æ»s¸ê®Æ¦Ü
¥t¥~¤@­ÓWorkbook¤ºªºWorksheet("¤u§@ªí1")¤º¡C


  If xB.Name = ThisWorkbook.Name Then GoTo 101


  If xB.Name <> ThisWorkbook.Name Then GoTo 101


§Ú±N=, Âà´«¦¨ <> ¦ý³£¬O¤£¯à¦¨¥\¡C

§Ú­n¤À¶}Raw Data ­¶¬O¦b¥t¤@­ÓWorkbook¤º½Æ»s¸ê®Æ¥h ¥t¥~¤@­ÓWOrkbook¤ºªº"¤u§@ªí1"¤º¡C

"Raw Data" Worksheet¬O©T©w¦WºÙ,¦ÓWorkbooks¦WºÙ«h¬O¤£©T©w¡C
¦Ó"¤u§@ªí1"¬O©T©w¦WºÙ,¦P®ÉWorkbooks¦WºÙ¤]¬O©T©w¥s"Data.slxm"ÁÂÁÂ

TOP

  If xB.Name <> ThisWorkbook.Name Then GoTo 101
§Ú±N=, Âà´«¦¨ <> ¦ý³£¬O¤£¯à¦¨¥\¡C
...
stephenlee µoªí©ó 2020-9-15 10:54


  If Not xB.Name = ThisWorkbook.Name Then GoTo 101

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD