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 RepliesI 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
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 RelatedThe 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 RelatedI 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'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!)
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.
I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.
Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.
Sub SearchAllXLSFiles()
Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook
On Error Resume Next
Set wbDestiny = ThisWorkbook
I have always saved each macro on a single workbook, saved the workbook using the macro name, so I can go use that spreadsheet for a specific issue. Now, however, my job role is changing to where I have to do a lot of things like get a file from Bob . . . slice and dice and then forward it on to Carol, etc. Then I have to save spreadsheet A to this directory, spreadsheet B to that directory . . . .
I am sure there is, I just can't get my head around it to have one spreadsheet that has ALL my macros that would be available to me on other spreadsheets? I tried using "show macros in all open workbooks"
I currently have a 70 page spreadsheet with similar data that needs representing in pie chart format. All the column headers are the same and I only need 2 columns (A and T).
There are however different numbers of rows. Is it possible to write a macro to detect the number of rows before it draws the relevant pie chart (I've managed the pie chart bit!!)
See attached example, same concept but cant post real data.
I have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
If I want to create a function or sub that can be used by any userform or macro that I create for a workbook:
Where would I define that function? Within the Module?
Do I define it as Public?
I have a macro that formats an imported report and I have inserted it's code into several other routines I have written.
Since there is always the possiblity of changes occurring, is there a way to refer to this macro in a second (or third or fourth) routine so that if a change has to be made to the original macro, I won't have to change it everywhere?
I have been asked to create a spreadsheet that has a front sheet where you can free type search criteria and then it will populate rows below with all search results from the remaining sheets within the workbook, for example;
In cell N15 I type the search criteria and hit a button
In cells F31-O31 it returns the following information;
CategorySub-CategoryCourseDrop Down Option 3SolutionFurther information or support
I have little to no experience in writing macros
This report spreadsheet is exported from SAP. Since SAP has a limit number of displaying the columns, there are 40 headings (= 40 columns) in the report has been broken down to 4 rows of headings.
How to use macros to move 3 those extra rows to one row and the number still matches each column? I also attatch the file for reference.
I am using the code below to open multiple workbooks in their own instance of Excel.
On my system, as well as most of the ones I've tested it on, enabling macros in the "parent" workbook, i.e. the one that contains the code, leaves them automatically enabled in the new session of Excel, as though the settings are carried over.
However, recently I've found a few systems where this is not the case. Enabling macros in the parent workbook opens the others, and then I have to enable macros again in each of the new sessions. Is there a way to fix this? All of the systems mentioned above have macro settings set to "disable with notification" and this cannot be changed.