I want to be able to run this many times looping through different file names. The only thing that changes between file names is the number.
Application.Run "'C:Documents and Settingspadi4545DesktopProjectRuns10.xls'!main"
ActiveWorkbook.Save
ActiveWindow.Close
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
I am trying to run multiple macros from multiple workbooks. I've figured out how to open each workbook, but I can't figure out how to run a certain macro in each workbook called "Main".
View 3 Replies View RelatedI'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.
Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.
From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:
Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.
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.
[url]
When I open up a new workbook it automatically contains 3 macros that I mistakenly added when I was experimenting a few months ago. How can I remove these macros from the default new workbook?
View 3 Replies View RelatedI have a series of 24 files (for non-profit grants), each of which contains a macro to create a consolidation file of 29 departments (for each grant). Instead of opening each file individually I would like to create a macro in a different file that would open each file and run the consolidation macro in each file. So far I have been unsuccessful in compiling a macro to complete this task.
View 7 Replies View RelatedI've used a loop in one of my macros and the loop goes on endlessly. I've been trying to come up witha way to overcome this problem but with no luck till now. knowing that I'm new to VBA i decided to turn to the big guns at mr.exxcel to help me out. Here's the code i have. Any alternatives to this would be VERY VERY VERY USEFUL!!!
Sub smalloops()
Dim i As Integer
Dim compliant As Integer
Dim noncompliant As Integer
compliant = 0
noncompliant = 0
For i = 1 To 60
Do Until Cells(i, 2) = ("RECOMMENDATIONS")
Do While Cells(i, 3) 0
If Cells(i, 7) = 1 Then
compliant = compliant + 1
ElseIf Cells(i, 7) = 2 Then.................
The first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.
VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object
[Code].....
I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.
I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:
Application.Run "CodeWb.xlsm!DataFileOpen"
So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.
How can I use the code in CodeWb to handle all events occurring in DataWb?
Is there a way I can share the workbooks with macros built in the excel file?
View 1 Replies View RelatedIs there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
View 9 Replies View RelatedThis code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).
And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedI have 4 different workbooks that I want to merge into one workbook. I tried moving the sheets from these workbooks into one workbook but to my surprise all the vba / macros didn't transfer over to the other workbook.
I searched here but all I can find were people that wanted to make a summary workbook or merge workbooks into a worksheet which isn't what i want to do.
I just want to simply merge these workbooks into one workbook without losing all the marco and vba stuff that are in each workbook.
I was just wondering whether VBA in Excel has an equivalent of the VB code
GrabData= Sheets....etc
in VBA and if so, is it possible to use it to grab data from different worksheets, not just sheets?
I would like to run a macro on all open workbooks except the workbook where the macro is saved. I thought I could just do the following but it does not run my macro on all the work books:
Sub apply()
For Each wb In Workbooks
If wbk.Name ThisWorkbook.Name Then
Debug.Print
nominee_name
End If
Next wb
End Sub
How do I continuously loop various sheets in different workbooks pausing then for 5 seconds on each sheet (not all sheets in each workbook).
View 2 Replies View RelatedI would like to loop through all spreadsheets in a folder, copy sheet "january" from each spreadsheet into a mastersheet. No idea how using vb all help appreciated.
i would like to change the name of the sheet to a cell reference before it gets pasted into the new sheet.
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
View 6 Replies View RelatedWhat I have is a master file that needs to pull info from other sources (a simple copy and paste)
those files are called Inventory_xxxx.xlsm . Inside the master file, there are cells with the number of the inventory.
What i need is a way to tell excel to check what number is in the cell, open the files with that number, get the info from the files and paste it back in the master file... and the part that i consider the trickiest, a way to loop it.
For example, lets say i have 2248 in cell A10, it should open Inventory_2248.xlsm copy the contents from cell N4, O4, P4, Q4, R4 and S4(Inventory File) into N10,R10,S10,T10 and U10 (Master file) respectively then in A11, there could be a 2250 so it should open the 2250 file get the info, paste it and so on.....
Now I don't even know if this is do-able or just impossible, been fiddling with various codes to no avail.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I'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 ?
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View 9 Replies View RelatedI would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
