Snasui Excel Forum

Code: Select all

Dim Lrow As Integer
Private Sub CommandButton1_Click()

    Dim sh As Worksheet, thisBook As Workbook, strThisbook As Variant
    Dim ob As Workbook, i As Integer, rRow As Long, tg As Range
    
    'Set ob = ThisWorkbook
    'ob.sheets(2).UsedRange.Clear
    strThisbook = Application.GetOpenFilename(Filefilter:= _
            "All File (*.*), *.*", Title:="Please select source file(s).", MultiSelect:=True)

    Set ob = ThisWorkbook
    If TypeName(strThisbook) = "Boolean" Then
        'MsgBox "Please select file(s)."
        Exit Sub
    End If
    'rRow = 1
    rRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    
    For i = 1 To UBound(strThisbook)
        Set thisBook = Workbooks.Open(strThisbook(i))
        Application.ScreenUpdating = False
'        For Each sh In thisBook.Worksheets
   Set sh = Sheets("BB")
          sh.UsedRange.Copy
           ob.Sheets(2).Range("A" & rRow).PasteSpecial xlPasteValues
            rRow = ob.Sheets(2).UsedRange.Rows.Count + 1 'ถ้า +2 จะได้ยอด Total ยอดสุดท้ายมาด้วย
           Application.CutCopyMode = False
'        Next sh
        thisBook.Saved = True
        thisBook.Close
    Next i
    
      Application.ScreenUpdating = True
    
    MsgBox "รวมข้อมูลทุกไฟล์ให้เรียบร้อยแล้ว กรุณาตรวจสอบข้อมูลอีกครั้ง"

End Sub