VBA for importing data from multiple wordbooks to one sheet

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Do the source files all have a sheet named "Summary of the Year"? Does your destination workbook have a sheet named "MASTER"?
 
Upvote 0
Yes.

I forgot to mention that the sheets of the input files are locked.
 
Upvote 0
Do you mean that "Summary of the Year" sheets are protected? If so are they all protected with the same password?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top