返回列表 上一主題 發帖

[發問] 資料複製貼上問題。

資料來源檔檔名不固定, 須手動開啟再執行程式:
Sub 複製()
Dim xB As Workbook, xU As Range, R&
Dim Sht As Worksheet, A, C%, xF As Range
Set Sht = Sheets("工作表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 "來源檔案無資料!  ": Exit Sub
'欄位::數量-號碼-名稱-日期
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

        靜思自在 : 發脾氣是短暫的發瘋。
返回列表 上一主題