Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub CopyFromWorkbooks()
- Dim DstRng As Range
- Dim DstWks As Worksheet
- Dim R As Long
- Dim SrcRng As Range
- Dim SrcWks As Worksheet
- Dim SrcWkb As Workbook
- Dim FileName As String
- Dim FilePath As String
- Dim Folder As Variant
- Dim Title As String
- Sheets("Quality Hours").Select
- Set DstWks = ActiveSheet
- Set DstRng = DstWks.Range("A2")
- Set RngEnd = DstWks.Cells(Rows.Count, DstRng.Column).End(xlUp)
- Set DstRng = IIf(RngEnd = "", DstRng, RngEnd.Offset(1, 0))
- FilePath = "xx"
- FileName = Dir("xx" & "\*.xlsm")
- If FileName = "" Then
- MsgBox "No Excel workbooks were found in this directory.", vbCritical
- Exit Sub
- End If
- Application.ScreenUpdating = False
- Do While FileName <> ""
- On Error GoTo ErrorExit
- Set SrcWkb = Workbooks.Open(FileName:=FilePath & "\" & FileName, ReadOnly:=True)
- For Each SrcWks In SrcWkb.Worksheets
- Select Case SrcWks.Name
- Case "Sheet1"
- DstRng.Offset(R, 0) = SrcWks.Range("D4")
- DstRng.Offset(R, 1) = SrcWks.Range("F4")
- DstRng.Offset(R, 2) = SrcWks.Range("I8")
- DstRng.Offset(R, 3) = SrcWks.Range("G6")
- DstRng.Offset(R, 4) = SrcWks.Range("A6")
- DstRng.Offset(R, 5) = SrcWks.Range("G4")
- Case "Sheet2"
- DstRng.Offset(R, 6) = SrcWks.Range("A2")
- DstRng.Offset(R, 7) = SrcWks.Range("B2")
- DstRng.Offset(R, 8) = SrcWks.Range("C2")
- DstRng.Offset(R, 9) = SrcWks.Range("D2")
- DstRng.Offset(R, 10) = SrcWks.Range("E2")
- DstRng.Offset(R, 11) = SrcWks.Range("F3")
- End Select
- Next SrcWks
- SrcWkb.Close False
- FileName = Dir()
- R = R + 1
- Loop
- ErrorExit:
- Application.ScreenUpdating = True
- If Err <> 0 Then
- MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & Err.Description
- End If
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment