Macro- Code To Transfer Data From Multiple Workbooks Within Folders

Sep 15, 2008

I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.

the folders are set up as follows,

there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.

for example a1 - k1

the code i am using transfers the file names but comes up with #REF! instead of transfering the data

Here is the

View 9 Replies


Code To Pull Data From Workbooks Across Various Folders

May 3, 2007

I've seen a lot of code on how to pull data from workbooks in the same folder. However, I'm trying to pull the data in workbooks in various folders.

For examply, I have one folder, called "Master Files". In this folder is 12 folders, one for every month of the year. Within these folders, is one for each week, with the last day of the week being the name for the folder. In other words, it's like \Master FilesApril4-6-07data.xls

Now, I know how to get the data I need if all files are within the same folder. But how would I write a macro to run through all of those files when they're in separate folders?

View 9 Replies View Related

Consolidate Data From Multiple Workbooks From Different Folders/directory

Feb 5, 2010

I was searching through the internet to look for some help with regards to consolidating data from multiple workbooks from different directories into a single worksheet called Summary.

I would like to copy the entire worksheet from different workbooks in different directories into this workbook I like to call Summary. To manually do it I have to open the workbook I want to copy the worksheet from. Right click on the tab and select Move or Copy, select the sheet I want to copy and select the workbook I want to copy this sheet to. Repeat this process for all the workbooks.

The next step is to copy and paste from these sheets into a main summary sheet. The data range from these sheets are starting from row 13 onwards and they may be of different size between Column A to Column W.

For example the workbooks are coming from :

S:Area 1West.xls from worksheet Andy
S:Area 1East.xls from worksheet Bill
S:Area 1North.xls from worksheet John
S:Area 1North.xls from worksheet Albert etc...

View 9 Replies View Related

Automatically Transfer Data From Multiple Workbooks Into Master Workbook

May 28, 2014

I am trying to automatically transfer data from nonadjacent cells (C1, B5,B10,B16,B22,B28) from multiple workbooks in a masterworkbook folder from A2:F2. I am a novice at VBA. I am not able to copy as Range("C1,B5,B10,B16,B22,B28") and the way it currently is coded only the last copied range (B28) is pasted to the master workbook. The data pastes to A2 in the master workbook instead of F2 where I want it. I need copying the cells from the workbooks into row 2 in the master bookbook. Here is what I currently have:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:ToolFolderWorkObjectives")


View 9 Replies View Related

Transfer Rows Of Data From Multiple Workbooks To Master Workbook Based On Value In Row

Apr 16, 2014

I have created timesheets for employees that work in our shop. Our company manufactures products for different industries, such as mining, wind power generation, general industrial applications, and so forth. I modified some time sheet templates I found for excel to accommodate our company's actions. Each employee has their own workbook, in which the months are separated into different worksheets. Each sheet is divided further into weeks and in each weekly section the areas of information are divided as follows:

A / B / C / D / E / F / G / H / I / J / K / L / M
Work Sector / Workshop or Fieldservice / Scope of Work / Job # / Reg or OT / Mon / Tue / Wed / Thu / Fri / Sat / Sun / Total

There are 7 workbooks (one for each employee), each with 12 sheets (one for each month). I want to create a master sheet that will pull information from everyone's timesheet if they worked on a particular job. In other words, I would like to type a job number into a cell, then have excel look through everyone's timesheets and pull over only the rows of information that contain that job number

View 12 Replies View Related

Consolidate Data From Multiple Files Within Folders & Sub Folders

Aug 16, 2009

I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.

The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.

I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.

Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).

Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?

Sub Click2()

Application. ScreenUpdating = True
Dim MCDrow As Long
'Dim SubFolders As String
MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row

Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
'SubFolders = True
Fname = Dir(Fpath & "*.xls")
Do While Fname <> ""

ThisWorkbook.Sheets("Client Data").Unprotect

Workbooks.Open Fpath & Fname
Worksheets("Client Data").Activate
Worksheets("Client Data").Unprotect

View 6 Replies View Related

Transfer Data Between Workbooks

Jan 23, 2009

I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.

However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.

View 2 Replies View Related

VBA Data Transfer Between Workbooks

Apr 8, 2008

I have a series of of quesitons but honestly, I think it'll be answered by ONE question.

This is the my request as described below:

Bob, Jim, and Lisa are Project Managers (PM). Each PM has a Project Template Workbook with required info they need to fill out PER job. This Template workbook has a VBA button that submits their info to the PM Master File.

The PM Master file is a seperate workbook that sums up each project as filled out by the Project Template Workbook.

What I need is the code to either copy/paste the values from the template to the master file ensuring that the most recent addition of information goes to the next row and doesn't paste over the existing data.... or ... I need a different approach.

View 9 Replies View Related

Data Transfer Between Two Workbooks

May 6, 2009

I have two workbooks book1.xls and book2.xls

I want to load data in sheet1 of book2 to sheet1 of book1.

This sheet1 of book2 has a table of 10 rows and 10 columns.

View 9 Replies View Related

Use Excel VBA To Copy Multiple Files From Different Source Folders To Different Folders?

Jun 12, 2014

I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.

E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.

View 1 Replies View Related

Transfer Data From One Workbook To Several Workbooks

Jun 16, 2013

I have several hundred workbooks an am trying to write a macro to transfer data from another workbook "_TABLES.xlsm" to each of them. By using 'Record Macro' I have so far come up with -

Windows("12-01-01 Results.xlsm").Activate

Where "12-01-01 Results.xlsm" is the receiving book. My question is - what do I call a generic sheet so as I may drop the macro in all the other workbooks not labelled "12-01-01 Results.xlsm".

View 3 Replies View Related

VBA Code Error - Extract Data From Multiple Workbooks To Single Excel Spreadsheet

Mar 28, 2014

I have a folder with multiples excel sheets

Destination : C: Project CustomerExcel

I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.

New sheet would contain the information of each sheet eg. Column A2= file name

And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.

Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range

[Code] .....

View 5 Replies View Related

Making Macro That Will Create New Folders And Then Save In Those Folders?

May 21, 2014

I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.

As an brief example.

Cell "B3" on the master(worksheet) contains a job name I-40 bridge
So initially I would like the Folder name and file name to be I-40 Bridge

However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.

I am currently working with Excel 2007 Pro edition on windows 8. I

I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.

Public Sub SaveAsA1()
ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub

View 4 Replies View Related

Macro To Create Multiple Workbooks Based On Data

Jul 31, 2013

I have a worksheet with the following columns:



This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.

E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed

View 3 Replies View Related

Processing Data In Multiple Folders

Mar 8, 2007

I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.

Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
With ActiveSheet

View 9 Replies View Related

Macro To Open Specific File By Searching Multiple Folders

Oct 11, 2013

I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.

The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".

I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.

So far I have the following:

Sub Open_ESCL_Report()

Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub

Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx

View 3 Replies View Related

Macro Copy Data From Multiple Workbooks Into Master Workbook

Aug 4, 2012

I have 17 workbooks which contain similar data and formatting. The only variance is the number of rows of data in each. I want to write a macro in the Master worksheet that will accomplish the following

1. Create a worksheet name in the master workbook with the same name as the workbook that it's pulling data from.
2. Copy over the column headings from columns A:P to the newly created worksheet on the master workbook
3. Copy over all rows that contain the word "down" under column D titled Operational status

The naming convention for the master workbook will be titled "All Switches". The naming convention for the worksheets containing the data are titled Port_Map_on_tiegcsw01, Port_Map_on_tiegcsw02, Port_Map_on_nypgcsw01. There are other names , but I can reference the code an update accordingly.

I found some searches on the forum for creating a macro to copy data between workbooks, but not quite sure how to write the macro for the three functions.

View 2 Replies View Related

Open Workbooks In Two Folders

Jun 24, 2009

I have a script below that opens all the workbooks in a folder based on a msgbox. The script works as is. However what I would like to do is remove the msgbox and have the script go back and forth between the folders.

Example it would open one workbook in the (Large Area)folder call my other script (Hazleton_Data_Conversion) Return Open a workbook in (Varsity) Folder Call the
my (Hazleton_Data_Conversion)and repeat the process till all the workbooks in both folders have been processed.

Like I said this script works with the msgbox I just like to change to without msgbox and to flip back and forth.

View 3 Replies View Related

Macro Or Userform For Extract Data From Multiple Workbooks By Giving Date Range

Apr 2, 2014

I jst need to console data from different workbooks to one worksheet. There are 30 workbooks & each workbook contains one worksheet name as Data. The work i am currently doing mentioned below step wise

1) Open workbook
2) Apply filter on whole data
3) Select dates from "J" Column (E.g 02/10/2014 to 02/20/2014)
4) Then Copy whole filtered data
5)Paste into New workbook
6) Open another worbook
7) repeate Step # 2,3
8)Then paste whole data in below the first data New Workbook (In which we have paste the first data below that i will paste this data)

These steps i have to do manually it takes almost 1hr daily After pasting all 30 workbooks data in one workbook i have to make pivot of it & sent it to my Manager. What i need

I jst want a excel macro that will ask me date range from which i have to pull data from all 30 workbooks.

Once i gave date range will jst click on button say extract or pull. It willl console whole 30 workbooks data from the given date range in a one worksheet.

View 13 Replies View Related

Code For Transfer Data

Dec 22, 2009

i need your help by a code to transfer the data mentioned in ( main ) sheet ( in attached file ) to the ( 8 agents ) sheets which addressed (A,B,C,D,E,F,G,H) ( in same cells for easy using the code ).

View 10 Replies View Related

VBA Code For Transfer Of Data

Apr 17, 2006

We have a worksheet entitled 'Data Output' that has the raw data in column B. The data represents temporary input from another worksheet that serves as an intake form. Each person's intake data will temporarily populate column B of the Data Output worksheet. When each intake is complete, I want Excel to take the data in Column B and find the next available empty column in a worksheet called 'database page 1' and populate it with the data starting at row 3 downward. When the last column in database page 1 worksheet is reached, I need for Excel to go to 'database page 2' worksheet and begin populating the next empty column and so on.

Is there anyone within your forum or group that can help me write the VBA code for this and briefly tell me where in Excel to insert the code. I know formulas basically well, but have never used VBA.

View 5 Replies View Related

Moved Workbooks To Folders Now User Form Subsript Out Of Range

May 19, 2008

The attached workbook has a userform which was searching the same folder where this file is located for entries in columns "A" and "B" of files named in column "I" (workbook1, workbook2 etc).

I have this placed on a sharepoint and want to move the files into specific folders (folder1/workbook1, folder2/workbook2 etc.) so I can set them as view only folders to stop unuathorised editing.

I am now getting a "Subscript out of range" error after doing a search. I can see it's a problem with the file name as it's pulling it from text in a cell but can't work out!

View 9 Replies View Related

VBA Code To Transfer Selected Data To List / Manifest

Feb 11, 2014

I am currently in the process of creating a manifest system.


What I would like to happen is, when I input information onto one fo these sheets and click the Macro button to send an email, that selected information gets automatically transferred onto the first blank row on another sheet (TRANSPORT SHEET)

The data I want to transfer is:

J8 A1
F6 B1
G19 C1
J6 D1
H11 E1
C9:19 F1
E9:19 G1
J15 H1

For Column J on the spreadsheet I require the name of whoever input the data ( name of excel user)


View 1 Replies View Related

Propagate And Run Vba Code Into Multiple Workbooks

Apr 24, 2007

I support an excel file that has a bunch of vba code behind including calls to msquery. The customer used this excel file by opening the original pulling in data for a specific client and than saving the file to a different name. The result is she has multiple copies of the file.

It currently has two problems:I need to change the code in one of the subroutines to fix a known problem. This of course involves opening up the Visual Basic editor (from the excel file), finding the code to change and making the code change.We have just upgraded to Office 2003 and MSquery is looking for xlquery.xla which is no longer needed in 2003. The problem this causes and its solutions are described at Microsoft Support here. I have done the quick fix which is putting the xlquery.xla where excel is looking for it, but I want to do the correct fix which involves running the following code in every workbook that accesses xlquery.xla

Sub DeleteQueryDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.Visible = False And InStr(1, n.Name, "QUERY", _
vbTextCompare) > 0 And InStr(1, n.Name, _
"Query_from", vbTextCompare) = 0 Then
End If
End Sub

and than making sure you save the file as office 2003.Both of these things are much to complicated and time consuming for my customer to do for each individual excel workbook.

Which leads me to my question: Is there a way to automate this?

View 8 Replies View Related

Transfer Data Between Multiple Spreadsheets?

Jun 20, 2014

I am creating a MIS for my team. But I am now struck while creating dashboard. I have a workbook with 12 raw data spreadsheets for specific months. Now what I want is that in 13th sheet if I select January then 1st cell in 13 sheet will show the value January cell 1 value. However if I select February then 1st cell in 13th sheet will show the value of February cell 1 value.

View 7 Replies View Related

Transfer Of Multiple Data From List Box?

Feb 2, 2012

I have a list box in sheet 3 of my workbook containing a list of various vehicles and I want to be able to select certain data from it and transfer it to a single cell on sheet 2. Is there a formula for this. I tried entering the data into individual cells and then copying it to a single cell but it keeps pasting all cells seperately.

View 2 Replies View Related

Run The 'loop Through A Folder' Code On Multiple Workbooks I Receive

Jun 5, 2008

I am trying to run the 'loop through a folder' code on multiple workbooks I receive.

The workbooks I receive are full of drop downs that have associated values of 1-3 on the first sheet. (About 100 in total) This particular workbook has the drop downs on one worksheet and the numeric results on another worksheet 'Results'

The second workbook 'Totals' (very basic) , just referenced each 'Results' worksheet and had equations that averaged all the drop downs cell by cell.

I would love to be able to use the 'loop through a folder' code to open them and then average them on the 'Totals' sheet. The main reason is that I am delegating this to another person and would like to eliminate the risk or human error. ( unless it is my own)

I am a total VBA n00b. Any assistance would be appreciated.

If needed I can upload the code or sheet as an example.

The base folder would always be the same. ie c: estresults*.xls

The naming would be very similar.

This loop code seemed relevant as it did not seem to require any file naming and would run through a folder and process all XLS files.


View 14 Replies View Related

Transfer Data Between Worksheets Using Multiple Criteria?

Feb 26, 2013

how to Transfer data between worksheets using multiple criteria?

View 1 Replies View Related

Multiple Userform - Transfer String Data

Mar 18, 2013

I am working with several userforms and try to transfer data obtained in one userform (lets call it Userform1) to another (Userform2).

Specifically, the idea is to have a commandboxA where the user can choose several options and the selection will define a string, and I want to use this string in Userform 2 to define a text.


The user chooses "solid" in the commandbox and I define the string as "rock"

Dim structure As String If commandboxA1.value = "solid" Then structure = "rock" etc...

And in Userform 2 I would like to combine the string structure with other strings, e.g.

If commandbox.value = "example" Then example text = "example text@ & structure & "text"

However this does not work, because the string which I have defined in Userform1 is not defined in Userform2. How would I define the string specifically, such that I have access to it in both userforms? How can I define the string as public? I tried several times, but could not get it to work.

View 3 Replies View Related

Data Transfer From One Worksheet To Multiple Worksheets

Mar 25, 2007

I want to transfer the data of one worksheet (to be added everyday) to multiple sheets.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved