Copy several cells form multiple workbooks into a new workbook.

[migces]

Hello,

I have many workbooks, each with only one worksheet, containing a wide range of data related to one candidate for a work position. I need to copy specific data from each candidate into a new workbook that will contain all the candidates so i can look in one place for all the relevent data for all candidates, rather than opening each individual candidate file.

For example:

I need to copy the cells D7,K10:K13,K16:K21,K24:K29,K34:K37 and K39, from each candidate workbook, into a row on the new unified workbook.

Hope it made sense.

Thx for any help.

Cheers

 

[mumps]

What is the full path to the folder containing the workbooks? What is the name of the source sheet? What is the extension of the source workbooks (xlsx, xlsm)? Are the source workbooks the only workbooks in that folder? What is the name of the sheet where you want to paste the data?

 

[migces]

What is the full path to the folder containing the workbooks? What is the name of the source sheet? What is the extension of the source workbooks (xlsx, xlsm)? Are the source workbooks the only workbooks in that folder? What is the name of the sheet where you want to paste the data?

Thx for the reply.

The path will be c:\workfiles
The source sheet name is by default "Pagina 1" but there can be workbooks with different sheet names depending on the original creator
The extension of the files, sadly it also depends on the original creator. Some are .xls and others .xlsx
The source workbooks are the only files on the folder
The name of the sheet where all the data will go is "Work Candidates"

Thx again.

 

[mumps]

We need a way to reference the source sheet. You said that the source workbooks contain only one sheet. The name may be "Pagina 1" but is the code name (in red) always Sheet1, for example: Sheet1("Pagina 1")

Do the source workbooks actually contain only one sheet or do they contain one sheet with data and others that are blank?

 

[migces]

We need a way to reference the source sheet. You said that the source workbooks contain only one sheet. The name may be "Pagina 1" but is the code name (in red) always Sheet1, for example: Sheet1("Pagina 1")

Do the source workbooks actually contain only one sheet or do they contain one sheet with data and others that are blank?

Hmmm.. i see what you mean. I have arround 200 of those workbooks. I've only opened about 20 or so and all of them only had 1 sheet, named "Pagina 1" and as such i assumed they'd all follow that pattern. I can have someone open all of them and eliminate the blank sheets if they exist.

This seems kinda weird, but this is part of a selection process for jobs positions wich many people worked on and it all landed on me for this data compiling stage.

Thx again

 

[mumps]

If they can delete all the blank sheets leaving only the sheet with data, please let me know and I will try to come up with a solution.

 

[migces]

Yes, they'll do that. All the workbooks will only cointain the 1 sheet with the data.

Thx.

 

[mumps]

Place this macro in a standard module in your destination workbook and run it from there. The macro assumes that all source files will have either an "xls" or "xlsx" extension. If that is not the case please let me know.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Work Candidates")
    Const strPath As String = "C:\workfiles\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            ActiveSheet.Range("D7").Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            ActiveSheet.Range("K10:K13,K16:K21,K24:K29,K34:K37,K39").Copy
            desWS.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[migces]

Hi.

Thx very much for this.

I ran it and come across 2 errors:

The first is a run time error 9: https://ibb.co/t81xTMg on this line https://ibb.co/3rZr3RS

If i delete that line and run it again i get error 91: https://ibb.co/TBfQgX6 on this line https://ibb.co/XJYTMND


Cheers.

 

[migces]

Hi.

Thx very much for this.

I ran it and come across 2 errors:

The first is a run time error 9: https://ibb.co/t81xTMg on this line https://ibb.co/3rZr3RS

If i delete that line and run it again i get error 91: https://ibb.co/TBfQgX6 on this line https://ibb.co/XJYTMND


Cheers.

Oh, nvm the above. I solved it by changing the destination workbook active sheet name from "Work Candidates" to the actual sheet name by default.

Final question: any copy command to only copy the cell value and not all the cell properties like formats and such?

Thx again.