macro to pull all data from every file in a folder and copy Data into the open workbook?

[tonywatsonhelp]

Hi Everyone,

I have a Work Book Called "New Document"

I want to open and copy data into this document from lots of other workbooks?

Basicly I get employees timesheets sent over to me every week, they are all the same and currently the data is being put into my excel document manually,

I want to be able to run a macro that will pull all the time sheet data into my workbook.

So here's what I need,

The workbook will be opened and is called "New Document" the sheet I want the data put into is sheet2 and its from row 2 so Cell A2

the timesheets will all have different names but I don't know what they will be.

The folder I'm need the data pulled from is Called "Times" and is stored on my pc in my documents (if you need me to edit the path no problem, but if it easier to just type it I'm just "User1" C drive all the standard set up.

So all I need is a way to do this:

Open Each file in the folder "Times" copy the following cells "C2:f2, B7, H5, L6, M7 N8:n12,

Then paste this information in that order into the open workbook "New Document" sheet2 Starting from Cell A2, (Now I need it to paste all this data as one long row, and there might be data in Cell A2 so the next available row will need to be found for each one!

So if anyone can help me do this that would be great.

Thanks

Tony

 

[bertie]

The code below loops through all the Excel files in the specified folder.
I have used a constant variable at the top of the code so you can easily edit the folder path.
NB Remember the trailing backslash in the folder path.

I have assumed the data to be imported is on the first worksheet of the source file.
i.e., Worksheets(1) Edit this if necessary. See highlighted (red) below.

To use:
Make a copy of your workbook.
Press Alt+F11 to open the vba editor window.
Click Insert => Module.
Copy and paste the code below.
Press F5 to run.

Option Explicit


Const FOLDER_PATH = "C:\temp\Users\User1\My Documents\Times\"  'REMEMBER END BACKSLASH


Sub ImportWorksheets()
   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim rowTarget As Long         'output row
   
   rowTarget = 2
   
   'check the folder exists
   If Not FileFolderExists(FOLDER_PATH) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet2")
   
   'loop through the Excel files in the folder
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
      
      'open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets(1) 'EDIT IF NECESSARY
      
      'import the data
      With wsTarget
         .Range("A" & rowTarget).Value = wsSource.Range("C2").Value
         .Range("B" & rowTarget).Value = wsSource.Range("D2").Value
         .Range("C" & rowTarget).Value = wsSource.Range("E2").Value
         .Range("D" & rowTarget).Value = wsSource.Range("F2").Value
         .Range("E" & rowTarget).Value = wsSource.Range("B7").Value
         .Range("F" & rowTarget).Value = wsSource.Range("H5").Value
         .Range("G" & rowTarget).Value = wsSource.Range("L6").Value
         .Range("H" & rowTarget).Value = wsSource.Range("M7").Value
         .Range("I" & rowTarget).Value = wsSource.Range("N8").Value
         .Range("J" & rowTarget).Value = wsSource.Range("N9").Value
         .Range("K" & rowTarget).Value = wsSource.Range("N10").Value
         .Range("L" & rowTarget).Value = wsSource.Range("N11").Value
         .Range("M" & rowTarget).Value = wsSource.Range("N12").Value
         
         'optional source filename in the last column
         .Range("N" & rowTarget).Value = sFile
      End With
      
      'close the source workbook, increment the output row and get the next file
      wbSource.Close SaveChanges:=False
      rowTarget = rowTarget + 1
      sFile = Dir()
   Loop
   
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   'tidy up
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
End Sub




Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

Hope this helps,
Bertie

 

[tonywatsonhelp]

Hi Bertie,
This is just perfect! thank you very much, works exactly like I needed it to

big thank you

Tony

 

[RicoHockeyMan]

Hello,

This code is really close to what i'm looking for. The only thing different i need is instead of copying individual cells and pasting them in to 1 row, i need to copy rows and consolidate them.

If you could help me out, i am looking for a macro that will:

-open multiple workbooks (just like this code does)
-copy all of the rows that have starting with 2nd because the 1st row is headers (each workbook will have different number rows with data in them)
-Then past them in the same worksheet, one after another (just like this code does)

Please and thank you!

 

[su2009]

hi all,

i am looking for the same code as RicoHockeyMan. Anyone out there that can help. Thank you.

Hello,

This code is really close to what i'm looking for. The only thing different i need is instead of copying individual cells and pasting them in to 1 row, i need to copy rows and consolidate them.

If you could help me out, i am looking for a macro that will:

-open multiple workbooks (just like this code does)
-copy all of the rows that have starting with 2nd because the 1st row is headers (each workbook will have different number rows with data in them)
-Then past them in the same worksheet, one after another (just like this code does)

Please and thank you!

 

[Pitoun]

Hello Bertie,
Your script is wonderfull for me !

After the script ran through the tasks, I'd like to sort the results from area [A2 to J???] (??? = the last line on colon J) by smallest to biggest. Avoid sorting colons K to XFD.

How can I manage that pls?

 

[wakerider017]

This worked perfect. Just wanted to stop in and say thanks!

 

[MakisSk]

It's 2020 and I just finished a project using part of this code - with modifications to suit my needs. Thank you, Bertie!

 

[Usommer]

It is 2021 now, and after spending a day and a night with testing dozends of similar-looking solutions, trying to figure out a way to loop through 1.600 Excel files and pull out data from different fields, which I need as part of a project I do for a shop, at around 4 in the morning I found your code. It worked perfectly, thank you!!