Running Sub On Multiple Files
Dec 9, 2013I have made a module that I want to run on 1400 excel files in different directories. How can I do this?
View 1 Replies
ADVERTISEMENT
Running Macro Over Multiple XLSX Files
Oct 2, 2013how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01
*run script (some calculations, copy/pasting, my script for this is working fine)
*save file under a different name (i.e. Subject_01_processed.xlsx)
*close file
*again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
Running Macro Across Multiple Files In Same Folder
Apr 7, 2009I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.
Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?
Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook
MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run"
MyFileName = Dir(MyPath & "*.xls")
Do Until MyFileName = ""
Workbooks.Open MyPath & MyFileName
Set MyBook = ActiveWorkbook
Application.Run "ExcelDietMacro"
MyBook.Save
MyBook.Close
MyFileName = Dir
Loop
End Sub
Sub ExcelDietMacro()
'
' ExcelDietMacro Macro.........................
Work On Other Files While Running A Macro?
Nov 28, 2008I have designed a macro which consists of a form through it takes the input into excel sheet. The macro is running fine and I dont have any problems with it. The problem I am facing is that after I start running this macro, I am not able to access any other open excel files. Is there any option to make all other excel files available during running a macro?
View 3 Replies View RelatedSlow Vba Running With Xlsm Files?
Aug 29, 2013i have a simple question regarding vba code involving opening/eleboration/closing of different excel workbooks' format. I saw that with xls/xlsx the macro works with relatively high speed, but it is very slow when it comes to xlsm file. Is there an explanation? Does a command which enhance speed for xlsm file exist?
View 8 Replies View RelatedCopy And Rename Files In VBA By Searching Multiple Folders For Files?
Jan 4, 2014how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
Running Multiple Modules Across Multiple Workbooks With Multiple Sheets
Feb 19, 2012I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
Save Ranges In Multiple Files To Text Files
Dec 21, 2007I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
Convert Multiple Files To Text Files
Sep 26, 2007I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
Send Multiple Files Based On Criteria To Multiple Email Addresses?
Jun 29, 2014I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook
Nov 7, 2009I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
Running Multiple Applications
Apr 4, 2014I am trying to run multiple applications. I can run one application i.e.
Application.Run (Sheets("Sheet2").Range("A8").Value) but not multiple i.e Application.Run (Sheets("Sheet2").Range("A8").Value) & Application.Run (Sheets("Sheet2").Range("A9").Value) at the same time.
I have also tried Application.Run (Sheets("Sheet2").Range("A8:A9").Value) but to no avail.
Running Same VBA Over Multiple Sheets
Jan 12, 2008I have some code which I want to run on all apart from one sheet called 'List'. At the moment I am selecting each sheet before running code for the specified sheet. This doesn't allow for any sheet name changes etc. or additional sheets. Is there a quicker way of doing this rather than having a sub to manually select each sheet?
Here is an example of my code for a button on the sheet called 'List'. The only thing Sheet1, Sheet2 does in this code is select that sheet.
Create Multiple Files For Multiple Groups Of Data
May 9, 2008I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
View 2 Replies View RelatedRunning The Same Test On Multiple Variables?
Jul 12, 2013I am trying to run the same test on multiple cells, to get the column letters of cells while there are less than 78 columns in use (these column letters will later be used for the Range().Select work involving copy & paste-ing into a word document). I originally thought I could use GoTo statements in conjunction with variables but, having researched it and coming across this, I think I need to find another approach.
At present I have a long section of code that looks like this (I am writing and testing at present so what i make will be put into a larger macro):
VB:
Sub FindColumnLetters()
Dim i01Feb As String, i02March As String, i03April As String
Dim NumbTemp As Integer, NumbTemp2 As Integer
[Code]....
However VBA didn't like the use of variables with the GoTo function and there are not decent ways around. how I can do the original but in less space? FYI the codes above are repeated for 3 columns, where I am actually doing this for ~20 column refs so the space it takes is HUGE at present.
Running One Macro For Multiple Workbooks?
Apr 7, 2014I have approximately 30 workbooks for which i open and run a macro to change the look and feel for client perusal.
I normally open each of the 30 workbooks and run the macro for each one.
Is there a way I can run the macro to apply to all workbooks at once?
Code Running For Multiple Worksheets
Jun 27, 2014I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.
[Code]........
Multiple Timer For Running Workshop
Jul 12, 2014I am running a spreadsheet to keep track of workshop technician times. The code I am using I have copied and modified from this forum works really well but I need the timer to go in minus time instead of stopping at 00:00:00. It work be really great if the timer cell changed colour it identify that the Technician had run over the allotted time.
[Code] .....
Running Macro In Multiple Worksheets?
Dec 20, 2013I am seeking code that will go to Sheet 4 (named "Extract"), copy a1:a5 and return to the active cell the macro was launched from and copy the values in.
The challenge I am having is how to get the code to return back to the cell it was launched from since I want the flexibility to use this macro in multiple tabs/sheets to copy the info into ANY selected cell within ANY sheet. As it is now, I have to go change the sheet name each time for the then active sheet.
I've attempted to insert a generic worksheet reference, however I end up with a debug issue.
Sub Macro10()
'
' Macro10 Macro
[Code].....
Running Average For Multiple Values
Apr 25, 2006building a spreadsheet to track instructors' classes' test scores. I have 3 basic variables: 1.) The instructor's name 2.) The course taught, and 3.) The classes' scores for that particular course. The 3 courses each have 3 different variations of the end-of-course test (9- total tests.) On the last page of the excel spreadsheet, I have a block for each of the instructors. I've been trying (with no success) to keep a running average of each instructor's class test average for the 3 courses. The part that is stumping me is associating the instructor's name with their class average, and then averaging all of their grades on that page together; the main reason being that this document is living, and new grades are entered almost daily. I tried using variations of the count function with no luck, other than to tell me how many times a particular instructor has tested in a particular test. Please help, I have the feeling that the function I am looking for is way-above my skill-level in Excel.
View 9 Replies View RelatedRunning Macro On Multiple Sheets
Jun 30, 2006I would like to run the code below on selected sheets in my workbook:
Range("B9:AW38, AZ9:BE38, b3").ClearContents
I tried using this but it doesn't work:
Sub clear()
Sheets( Array("sheet A", "sheet B", "sheet C")).Select
Range("B9:AW38, AZ9:BE38, b3").ClearContents
End Sub
Running Percentage With Multiple Criteria
Mar 26, 2008I would like to have a running percentage calculated in cell P3 for the attached file. Cells C3, E3, G3, I3, K3, M3 show the number of correct picks. Cells D3, F3, H3, J3, L3, N3 multiply the number of correct picks by a given value in row 1. For each round there are a possible 32 points. I would like to use some sort of IF Statement to have a running winning percentage calculated for each round where points are achieved.
Example:
Round 1 - #picks correct / 32 possible points
Round 2 - # picks correct from Round 1 + # picks correct Round 2 / 64 possible points
Round 3 - so on until round 6 with total picks correct overall / 192 possible points
Running Multiple Criteria Count
May 20, 2008Have list of employees and their dependents organized into the following columns:
Employee ID (unique to employee), Relationship Code (E=employee, S=Spouse, D=child), andBirthDate. Would like to add a column called Sequence Code which counts the family members (employee= 0, S=1, oldest D=2, next oldest D = 3, and so on).
Employee ID RelationshipCode BirthDate Sequence Code
100006641 E 2/5/75 0
100006641 S 1/2/75 1
100006641 D 3/9/89 2
100005555 E 2/5/77 0
100005555 D 1/2/89 2
100005555 D 3/9/88 1
Note that the list is sorted by Employee ID, but not Birthdate. Can this be done with a formula in the Sequence Code column? I really suck at VBA
Running Simple Macro On Multiple Sheets
Oct 22, 2013I need to run a very simple macro (filter by date, copy, and paste) on every worksheet in a workbook, and then place that data into a summary page at the "end" of the workbook. Most other posts had to do with some kind of formatting that would work in succession on the sheets linearly from left to right (for lack of a better way to put it). My problem, however, is that the data would need to be pasted into the summary worksheet, and then somehow the macro would come back to the next sheet (after the one it just did) and do it all over again, so on and so forth. The worksheets are arranged alphabetically, but other than that there is no sort of easily identifiable progression between them (i.e. Sheet1, Sheet 2, etc.).
View 9 Replies View RelatedRunning Multiple Macros With A Single Macro
Oct 5, 2007I have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.
View 9 Replies View RelatedRunning Multiple Pivot Tables With Same Macro
Aug 31, 2006I am trying to find a way to set up a macro that will allow me to pull in data - create the Pivot table - delete the table - then pull in fresh data (of a different row length - same number of columns) and create another Pivot table. I have tried to manipulate recorded code to no avail. Here is what I am starting with:
Const lngLastPossRow As Long = 65536
Range(Cells(1, 1), Cells(Cells(lngLastPossRow, 1).End(xlUp).Row, 24)).Name = "Data"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data"). _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array(" ", _
"Channel", "Sales/Exchange", "Mode of transp.", "Sold-to Party", "Material", "Data")
While it creates the first pivot table fine, after deleting it and starting again it wants to create the next sequence (PivotTable3) which crashes the macro. I must close the file and reopen to run it again.
Macro Running In OS X, Able To Run This Macro On 11 Files At Once
Aug 16, 2006I have this macro that a friend of mine wrote on a windows machine, and I can't quite figure out the code changes needed for OS X. For someone who knows how to do this, I'm sure it would only take a second, but I've been banging my head against the wall on this thing for too long.
Option Base 1
Sub Data_Compiler()
Dim FileSelected
Dim FileName
Dim CompiledDataArray(1 To 30, 1 To 53) ' Array to store our statistics
Dim Counter1 As Integer
Counter1 = 1
FileSelected = Application. GetOpenFilename("Your Files,*.xls", , "Select Files", , True)
If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit Sub
For Each FileName In FileSelected
Application.DisplayAlerts = False................
I can get a "open files" dialogue box to pop up, but I can't figure out how to make sure that multiselect is enabled. I need to be able to run this macro on 11 files at once.
How To Get Multiple Cells From Multiple Files Into One Workbook
Jan 13, 2014So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)
I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...
file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date)
file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.
is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...
Import Multiple TXT Files To Multiple Sheet
Apr 13, 2014I need to create a dynamic template for importing data from multiple .txt files to my workbook on a daily basis.
I recorded the macro for importing ONE txt files and starting to deveop it into looping through multiple txt files and sheets but to no avail.
1) Currently I have 4 named ranges
FileLocation = Location of my source file
FileName = All the file names of my txt files
SheetDestination = The sheet to be paste for each txt file (further explaination below)
FileFormat = .txt (for VBA purpose)
2) The macro recorded was in "ActiveSheet", thus I thought of looping through each sheet (put the activate code at the first line) for the codes to run.
3) ColA ColB
1SP.txt
2IND.txt
3US.txt
4
5
6
Column A is my SheetDestination, while Column B is the respective filename for source txt file. My final intended result would be simply macthing sheet 1 against SP, sheet 2 against IND, sheet 3 against US... so on and so forth....
Running Macro Based On Sheet Name (Multiple Sheets)
Dec 20, 2012I have multiple sheets in one file. I need a code (that will be ran at random) to look at all the sheet names and the ones that end in the word "Archive" to move to another file.
View 5 Replies View Related