Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Master excel workbook from multiple excel workbooks

  1. #11
    New Member
    Join Date
    May 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Master excel workbook from multiple excel workbooks

    Quote Originally Posted by JLGWhiz View Post
    Your original post indicated that you wanted to copy from Cell C6 to that last row and las colomn of the used range. To change the starting point of the upper left cell of the copy range, change the cell reference in red font below. You did not specify a destination range, so I assumed it to be column A in th master file sheets.
    Code:
    Sub cpySource()
    Dim sh As Worksheet, fName As String, fPath As String, wb As Workbook, lr As Long, lc As Long, ssh As Worksheet
    fPath = ThisWorkbook.Path & "\"
    fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            If fName <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPath & fName) 'Opens target workbooks one at a time
                Select Case wb.Sheets(1).Range("C5").Value 'This will designate the destination sheet
                    Case "Larry"
                        Set sh = ThisWorkbook.Sheets("Sheet1")
                    Case "Alice"
                        Set sh = ThisWorkbook.Sheets("Sheet2")
                    Case "SOF"
                        Set sh = ThisWorkbook.Sheets("Sheet3")
                    Case "Coke"
                        Set sh = ThisWorkbook.Sheets("Sheet4")
                    Case "SWELL"
                        Set sh = ThisWorkbook.Sheets("Sheet5")
                    Case "Britta"
                        Set sh = ThisWorkbook.Sheets("Sheet6")
                    Case Else   'In case no entry or bad entry is in cell C5
                        MsgBox "No Match in Cell C5 for " & wb.Name, vbExclamation, "MISMATCH" 
                        wb.Close False
                        GoTo SKIP:
                End Select
                Set ssh = wb.Sheets(1)  'Put source sheet in a variable
                lc = ssh.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column  'get last column of source sheet
                lr = ssh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row  'get last row of source sheet
                With ssh
                    .Range("B6", .Cells(lr, lc)).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)  'execute copy/paste
                End With
                wb.Close False
                    End If
    SKIP:
            fName = Dir
        Loop
    End Sub

    So I forgot to mention that the other workboooks are saved as microsoft excel csv documents. So right now your code is not working at all for me, could it be because of this?

  2. #12
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,901
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Master excel workbook from multiple excel workbooks

    Did you try changing this
    Code:
    fName = Dir(fPath & "*.xl*")
    to this

    fNa
    Code:
    me = Dir(fPath & "*.csv")
    to see if it made a difference? If not, try it. If the files were Excel workbooks saved as .csv then it should work.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •