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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
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.

Rich (BB code):
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Bertie,
This is just perfect! thank you very much, works exactly like I needed it to :)

big thank you

Tony
 

RicoHockeyMan

New Member
Joined
Jan 21, 2016
Messages
1
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

Board Regular
Joined
Sep 24, 2010
Messages
81

ADVERTISEMENT

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

New Member
Joined
Jun 23, 2015
Messages
17
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

Board Regular
Joined
Jun 10, 2015
Messages
74

ADVERTISEMENT

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

MakisSk

New Member
Joined
Aug 25, 2020
Messages
2
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
  2. Mobile
It's 2020 and I just finished a project using part of this code - with modifications to suit my needs. Thank you, Bertie!
 

Usommer

New Member
Joined
Apr 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,658
Messages
5,637,618
Members
416,976
Latest member
LL1300

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
Top