For Next Looping - Copying Multiple Spreadsheets
Sep 25, 2006
I am attempting to amalgamate several (100+) spreadsheets into a single spreadsheet. The data is very simple (only three columns, several rows). I have written a simple macro to copy 5 spreadsheets (called TIMESHEET1, TIMESHEET2 etc) into 1 spreadsheet called AMALGAMATED TIMESHEETS. I would like to expand this using a FOR - NEXT loop if possible - or any other way that someone can think of! I cannot use the variable TIMESHEETx as this is not accepted......
View 9 Replies
ADVERTISEMENT
Apr 3, 2013
My problem is automating a process that takes information from a single column with a range of D7:D77 in 22 spreadsheets and places this information in a summary spreadsheet as rows corresponding to the names of each spreadsheet. This is within the same workbook.
For example say spreadsheet A1 has a column from D7:D77 I would like this information in a summary spreadsheet with the row titled A1 and D1:D77 transposed. This would then be repeated for the remaining spreadsheets
View 4 Replies
View Related
Jun 28, 2006
I created a form that takes information from textboxes and places them onto a new worksheet. This is done about 20 times until I have 20 different worksheets.
What I'm looking for is a way to Copy only certain information from all the spreadsheets and paste it onto one spreadsheet. For example if column C has a cell with the word "Not Accepted" in it, that information needs to transfer to a new worksheet.
I'm looking to do this so that it will run through all spreadsheets and grab that specific information and dump it into a new sheet...
View 3 Replies
View Related
Jul 11, 2007
I have about 10 open spreadsheets (the name of these changes each time but has a certain format). What I need to do is write some code to cycle through all the open spreadsheets and copy and paste the data into a single master spreadsheet. All these spreadsheets have a single worksheet.
View 9 Replies
View Related
May 28, 2008
In my very crude code below....I would like to speed up the coding so by extracting data from some 800 CLOSED workbooks and pasting the data from Non-contiquous cells in an active workbook on one sheet. Can't seem to ge the = Excelmacro4 closed workbooks method to work so I went with opening each workbook, extracting data and then closing each workbook...way too slow.
Question 2. As the loop progresses...I would like to concatenate the text strings copied from the Source books Ranges G4 and G5 as they are pasted into a single cell in the destination workbook. Can't figure out how to either concatenate or xlpaste special in the coding.
Question 3. IF a user has a one of the workbooks OPEN, how do I copy that data as well other than On Error Resume Next?
Dim wbOpen As Workbook
Dim wbDest As Workbook
Dim WS As Worksheet
Dim i As Long
Sub GetdaData()
Application.ScreenUpdating = False
Application.EnableEvents = False
View 9 Replies
View Related
Feb 4, 2014
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Code:
Dim row As Long
Dim refname
row = 3
Do While Cells(row).Value ""
[Code] .....
View 3 Replies
View Related
May 1, 2007
I am looking to loop through a folder and open every workbook in the folder. For every workbook I would like to copy a range in a worksheet named "explain" and paste values into a separate file. All in all this separate file would be a consolidation of the information from the individual workbooks.
View 2 Replies
View Related
Jun 24, 2013
I have a matrix of coordinates in sheet ("layout") (eastings - V4:BR4, northings - U5:U100). I'm trying to run through each northing (row value U5:U100), for every easting (V4:BR4), by writing the coordinate value to sheet("ISO_model"), cell K18. Within the sheet (ISO_model) there is a model which gives an output in cell HA500. I'd like to write this output (for the specific easting and northing) back into the sheet ("layout"), so that I then have the x,y, z values to create a contour plot.
I've tried to start the look through the row of eastings, but it is not working.
Sub noise_contour()
For Each Cell In Range("V4:BR4")
'write coordinate into the model
[Code]....
View 5 Replies
View Related
Aug 20, 2014
The macro (used to) go down the list of spreadsheets and copy certain data from them into this master workbook. Now I'm getting a "'C:Users310108841DesktopTestFolder' could not be found" error and don't know why.
[Code].....
The error appears to happen here:
[Code] ....
What's more infuriating is that this appears to happen at random. I have been trying all afternoon to get it to work, and had no luck. I have literally just run it now, and it works fine. I can't believe it just works at random.
View 2 Replies
View Related
Sep 17, 2012
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
View 3 Replies
View Related
Oct 23, 2007
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
View 2 Replies
View Related
Sep 28, 2009
I am trying to find a vlookup formulae for multiple spreadsheets in excel.
I have the below data that i need to lookup, test and produce an output.
Main Spreadsheet:
Column B...........................Column C
A 0000564235.................... <Desc> <---- Desired output is Coumn C from searching all part numbers in each spreadsheet.
Spreadsheet 1:
Column B (Part No.) ..........Column C (Desc)
A 0000564235................................Hose
Spreadsheet 2:
Column B (Part No.) .......Column C (Desc)
A 02315645646 ............................Clamp
Spreadsheet 3:
Column B (Part No.) .......Column C (Desc)............
View 11 Replies
View Related
May 7, 2014
I was curious if there was a way to combine multiple spreadsheets that have multiple tabs all onto 1 spreadsheet?
View 9 Replies
View Related
Mar 20, 2014
I have a workbook that I add about 20-35 tabs in a month. To individually go through and protect the sheet will take too much time, is there another way where I can select multiple tabs so the worksheets can be protected?
View 8 Replies
View Related
Apr 28, 2008
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
View 9 Replies
View Related
Jun 20, 2008
after finishing a huge macro, the last step is to take row 1 of sheet 1 and insert it at the top of every worksheet.
This is the first way I tried it. It does absolutely nothing (except copy the row).
For Each ws In ThisWorkbook.Worksheets
Sheets("Sheet1").Range("1:1").Copy
ws.Range("1:1").Insert Shift:=xlDown
Next ws
This was another try, which resulted in a 1004 "Select method failed" error
View 9 Replies
View Related
Dec 1, 2006
I have a workbook to collate ratings for staff, there are three sheets, one is a summary of data, and two are sheets that can have data entered into them, called "Eligible" and "Ineligible".
I would like to write a macro to search cells in "Eligible" that contain a staff number, and copy this data to "Summary". If excel encounters a blank cell, i.e it has no staff number in it, it will move to she sheet "Ineligible" and perform the same operation, pasting the data below that which has been copied from "Eligible"
To further complicate things, every time the macro runs to populate "Summary" I would like it to clear the existing data in "Summary", to allow for deletions in "Eligible" or "Ineligible".
View 9 Replies
View Related
Jun 5, 2014
I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.
I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.
There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.
View 4 Replies
View Related
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
Apr 16, 2014
I have several excel spreadsheets by which I would like to add together certain values in the column with the same columns of all the other excel spreadsheets. So basically, If one of the values are "SELF" , I would like to calculate how many times that value occurs in all the spreadsheets. Any easier way to organize the data to be able to do this also.
View 1 Replies
View Related
May 19, 2014
How can i combine multiple Excel Spreadsheets into one Spreadsheet ?
View 3 Replies
View Related
Oct 16, 2008
I have a master spreadsheet that links to various other spreadsheet for budget figures. I have now created and updated the budgets for the 2008/09 budget year.
It is painstaking to update the links and browse to the new files one at a time and point them to their new folder. Is there a way I can update all of the link workbooks to the new directory in one fell swoop?
View 6 Replies
View Related
Dec 18, 2012
I need to combine these 6 spreadsheets into one single spreadsheet. It won't seem to allow me to paste it.
It needs to begin with 2012 and count down.
2011.xls2010.xls2009.xls2008.xls2007.xls
View 1 Replies
View Related
Mar 6, 2013
I have a workbook with two spreadsheets. One contains customer information. The other line items and shipping information. I would like to pull the customer number over to the line items spreadsheet. Both spreadsheets have the order number, but note there is a new line for every item in an order on the line items sheet. How do I accomplish this?
View 2 Replies
View Related
Jul 6, 2013
The company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.
We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-
Serial Number
Machine Type
Hours Run Time
Reason for Visit
Reported Problem
etc, etc
All the forms are identical so all the fields are identical
Field Service Record Spares 2013 Master Rev4 (2).xlsx (this is the correct file). The forms are then saved on a central server folder and stored in month order.
Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and paste all the information into the master sheet??
We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.
View 6 Replies
View Related
Feb 3, 2010
I currently have a macro that can be used by multiple spreadsheets. Is there that I can execute the macro within any specific spreadsheet without saving it in each spreadsheet. I just want to place it in a general spot so that in case there is modifications, I only have to change it in one place.
View 14 Replies
View Related
Oct 1, 2012
I have created with most of it being done by Austrada (who has done a great job) code to copy multiple spreadsheets into 1 spreadsheet. We have run into a error Run Time Error '9'. Subscript out of range.
Sub CopyData()
'----------------------------------INFORMATION----------------------------------------
'You need to make sure you have activated the Scripting Runtime reference for the FSO to work
'--------------------------------------------------------------------------------------
Dim fso As New Scripting.FileSystemObject
[Code].....
View 4 Replies
View Related
Aug 1, 2013
I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?
View 8 Replies
View Related
Apr 19, 2007
Is there a way to automate the importing of macros into a list of files ? I have a number of files that I want to add some code to and was wondering if this could be automated as opposed to adding the code to each file manually as it's going to be a regular job.
I've written a routine to loop through the list of files and open them, I just want to know how, if possible, i can automatically import the required macros.
View 6 Replies
View Related
Jul 27, 2007
I'm using Excel 2003:
I have agents that want to "own" records depending on the zip code. I want to dynamically assign agent to a record based on the zip code. If there was only one agent per zip code, this could easily be done with Lookup. However, in many instances there will be multiple agents for a zip code. In these cases, I was hoping to assign the agents to each record in a round robin fashion.
My agent/zip table would may look something like this:
37011 Smith
37013 Jones
37023 Herrman
37025 Martin
37025 Slater
37025 ORiley
37028 Phelps
37028 Trenton
37029 Reagan
I would then have another sheet made up of home addresses that each have a zip-code. If the record contains zip 37013, I can easily assign that one to Jones. However, if the zip in the record is 37025, there are three agents sharing that zip. So for the first record with 37025, I would assign Martin. The next occurence of 37025 would be Slater and the third would be ORiley. The fourth occurence would go back to Martin and so on.
I have found some versions of what I want to do on this site and others but can't find the looping capability.
View 9 Replies
View Related
