Page 1 of 7 123 ... LastLast
Results 1 to 10 of 70

Thread: VBA for importing data from multiple wordbooks to one sheet

  1. #1
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA for importing data from multiple wordbooks to one sheet

    Hi,

    I have some data within the range F78:U797 (16 columns and 720 rows) in multiple workbooks kept in one folder (C:\Desktop).

    I need a VBA help to import those data from all those wordbooks in that folder automatically in a separate MASTER worksheet for further processing. One column has "date"inputs; so I would like to have the list in chronological order too.

    Thanks in advance!
    Last edited by masud8956; Mar 11th, 2018 at 11:50 AM.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,123
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Is the folder containing the workbooks on your Desktop and if so, what is the folder name or are they in a folder called "Desktop" in C: Drive? What is the extension of the workbooks (xls, xlsx, xlsm)? Are the sheet names with the range F78:U797 all the same in each source file and if so what is the sheet name? Do the 16 columns have headers and if so in which row? Does the MASTER sheet contain the same headers in row 1 starting at column A? In which column (F:U) are the dates? Sorry for all the questions but they are necessary to try to find a working solution.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Quote Originally Posted by mumps View Post
    Is the folder containing the workbooks on your Desktop and if so, what is the folder name or are they in a folder called "Desktop" in C: Drive? What is the extension of the workbooks (xls, xlsx, xlsm)? Are the sheet names with the range F78:U797 all the same in each source file and if so what is the sheet name? Do the 16 columns have headers and if so in which row? Does the MASTER sheet contain the same headers in row 1 starting at column A? In which column (F:U) are the dates? Sorry for all the questions but they are necessary to try to find a working solution.
    Wow! Seems I have hardly given anything to work with. I am just a beginner. Please pardon my ignorance.

    1. The folder name will be Yearly Data. Location> C: drive> Desktop.

    2. File extensions are xlsx.

    3. Sheet names with the input range are identical. Each source file is a consolidation of one year. All source files are identical. The source sheet name is Summary of the Year.

    4. The 16 columns have headers in row 77 starting column F to U.

    5. MASTER sheet contains the same headers in row 26 starting at column B.

    6. The dates are in column G.

    Please tell me if you need more info.

    I really appreciate your effort. Thanks for the quick reply!

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,123
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Place this macro in a regular module in your destination workbook. It assumes this workbook contains a sheet named "MASTER". Save the workbook as a macro-enabled file.
    Code:
    Sub CopyRange()
        Application.ScreenUpdating = False
        Dim lastRow As Long
        Dim wkbDest As Workbook
        Dim wkbSource As Workbook
        Set wkbDest = ThisWorkbook
        Const strPath As String = "C:\Desktop\Yearly Data\"
        ChDir strPath
        strExtension = Dir(strPath & "*.xlsx")
        Do While strExtension <> ""
            Set wkbSource = Workbooks.Open(strPath & strExtension)
            With wkbSource
                .Sheets("Summary of the Year").Range("F78:U797").Copy wkbDest.Sheets("MASTER").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
                .Close savechanges:=False
            End With
            strExtension = Dir
        Loop
        lastRow = wkbDest.Worksheets("MASTER").Cells(Rows.Count, "C").End(xlUp).Row
        wkbDest.Worksheets("MASTER").Sort.SortFields.Clear
        wkbDest.Worksheets("MASTER").Sort.SortFields.Add Key:=Range("C27:C" & lastRow) _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With wkbDest.Worksheets("MASTER").Sort
            .SetRange Range("B26:Q" & lastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Thanks again!

    I ran the code and getting a RUN RIME ERROR 9.

    After debugging the following line was highlighted:

    .Sheets("Summary of the Year").Range("F78:U797").Copy wkbDest.Sheets("MASTER").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    I have saved the wordbook as xlsm file. The destination file (file containing "MASTER" sheet) is also located inside the folder
    Last edited by masud8956; Mar 11th, 2018 at 03:18 PM.

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,123
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Do the source files all have a sheet named "Summary of the Year"? Does your destination workbook have a sheet named "MASTER"?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Yes.

    I forgot to mention that the sheets of the input files are locked.

  8. #8
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,123
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Do you mean that "Summary of the Year" sheets are protected? If so are they all protected with the same password?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  9. #9
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Quote Originally Posted by mumps View Post
    Do you mean that "Summary of the Year" sheets are protected? If so are they all protected with the same password?
    Yes. They are protected with same password.

  10. #10
    Board Regular
    Join Date
    Oct 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for importing data from multiple wordbooks to one sheet

    Quote Originally Posted by mumps View Post
    Do you mean that "Summary of the Year" sheets are protected? If so are they all protected with the same password?
    Sorry. Made a mistake. "Summary of the Year" sheets are locked but without password. Those sheets have some unlocked cells but my input cells are locked.

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
  •