VBA for importing data from multiple wordbooks to one sheet

[masud8956]

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!

 

[mumps]

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.

 

[masud8956]

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!

 

[mumps]

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.

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

 

[masud8956]

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

 

[mumps]

Do the source files all have a sheet named "Summary of the Year"? Does your destination workbook have a sheet named "MASTER"?

 

[masud8956]

Yes.

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

 

[mumps]

Do you mean that "Summary of the Year" sheets are protected? If so are they all protected with the same password?

 

[masud8956]

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.

 

[masud8956]

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.