I 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?
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 have 7 different workbooks as part of my daily routine.
At present the first workbook is opened via the task scheduler, and the macro is run using the following code
[Code] .....
This then opens the next workbook and closes the current one. The next workbook then runs its macro again from a timed call
[Code] ...........
This repeats for 7 workbooks. The problem is, if a macro overruns - the whole schedule is out of sync when macro's are called depending on the time. What I would like to do, is link each macro by calling for the next one, and closing the current workbook. However, when i have tried to do this, the macro has always stopped when the workbook has been closed - the next macro doesn't run.
I was able to create a macro that formats the worksheet that I am working on. However, I would like this macro to be available to all worksheets that I would be downloading from a system.
I tried using the personal macro workbook, but when I ran the macro, it formats the personal worksheet and not the one that I would want to format.
I used general codes, but it pasted the formulas onto the personal.xls file.
I have a macro that at the moment just opens a workbook SpreadsheetA.xls,
Sub OpenA
Workbooks.Open Filename:=C:DocumentsSpreadsheetA.xls, UpdateLinks:=0
End Sub
SpreadsheetA.xls has a macro called "ExpectedRun" which is currently operated by clicking a commandbutton.
What is the code to run the "ExpectedRun" Macro in Sub OpenA?
Eventually I'll be opening a bunch of these workbooks, each with its own unique "ExpectedRun" macro, so is there any special command to call on the "ExpectedRun" macro for specifically the opened workbook?
I 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].....
I 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
how 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.
I 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 RelatedI 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 RelatedI 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.........................
I 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.
I 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 RelatedI want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
Ive just started using vba for excel and put together a very simple peice that uses the goalseek function. See below.
Public Sub calc_vol()
Dim currentcell As Range
Worksheets("sheet1").Activate
Set currentcell = Worksheets("sheet1").Range("bl2")
Do While Not IsEmpty(currentcell)
With Application
. Iteration = True
.MaxIterations = 100
.MaxChange = 0.00000001
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
End With
ActiveWorkbook.PrecisionAsDisplayed = False
currentcell.GoalSeek 0, currentcell.Offset(0, -13)
Set currentcell = currentcell.Offset(1, 0)
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
The code works great (a little slow - but fine for 65,000 lines), however i have nearly 100 workbooks that i have to run this macro for. I was wondering if anyone knew how i could manipulate it so that i could incorporate all 100 files into the macro, therefore running the macro once for all 100 files. If this is not possible is it possible to link multiple worksheets.
I am looking for a way to close a group on Workbooks that are in separate instances of Excel.
These Workbooks get opened similar to running a Powerpoint program, but much different usage than Powerpoint.
AS AN EXAMPLE:
I have 7 Workbooks open in Excel . . . Could be more or less.
Each workbook has 2 sheets, the "User Sheet" (not the actual name, as all user sheet names vary) and a sheet named "Setup." (Always named "Setup.")
On the "Setup" sheet I have a list of all the possible Workbooks that I want to close. For reference purposes only, I'll call this list "To Close". All 7 Workbooks would be listed, but not all of them would be opened. If any particular Workbook on the "To Close" list is not open, it would simply be bypassed.
Example of list: Column A
Row 25 has the name of Workbook A
Row 26 has the name of Workbook B
Row 27 has the name of Workbook C
And this would continue down to the last Workbook possibly open. I will know how many Workbooks could possibly be open.
Other workbooks could also be open, but they would not be closed by this macro. In this example, I would only want Workbooks A & B & C closed.
I would have rows 25 thru 27 (or more) listed in the same location on the "Setup" sheet of each Workbook.
Each "User Sheet" would have a macro called "Close Group"
The macro would have to search through all open Workbooks and when it found one of the Workbooks listed for closing, it would close that workbook. Then, the macro would look for the next Workbook on the "To Close" list.
The Active Workbook would also be on the list and that also needs to be closed. I would guess that this would be the last Workbook to close, but it really would not make any difference if the macro would still work. I think the macro has to have the active Workbook open to close the others.
After the list has been gone through, the macro would stop.
NOTE: Each Workbook has it's own "To Close" list. If it would help, I can leave the Workbook Name A off of the list in Workbook A. I would do the same on Workbook B, etc. Then the Active Workbook could automatically be closed at the end of the macro.
I have multiple files with data in that I need to merge, basically append the data from various files to an existing file. Does anyone know of a macro that can be written to do this?
View 10 Replies View RelatedI have been looking everywhere and I can't seem to find what I am looking for. I want to create a Macro that will go into a folder, search every Workbook (each with multiple Worksheets), and copy and paste the data onto a single Worksheet. The data that I want will begin in Row 3 of each Worksheet (the first two rows are titles and headers). The formatting will be the same for each sheet.
The only other part is that Column G has the following formula in each cell that I would like to keep if possible.
Code:
=IF(AND(ISNUMBER(F4),ISNUMBER(D4)),"Complete",IF(ISNUMBER(D4),TODAY()-E4,0))
I have 400+ workbooks in a folder. If they are all .xlsm (macro-enabled) workbooks, how can I import a macro (.cls file) to a particular sheet (sheet1) in each workbook using VBA so that when a user on another machine opens one of them, the macro works? Right now, I have this code to loop through all workbooks; I only need the import function to go in the middle. Copying and pasting or importing manually 400+ times is just plain unrealistic!
VB:
Sub OpenfileUpdate()
Dim strFile As String
mFolder = "C:files1Files"
strFile = Dir(mFolder & "*.xlsm*")
Do While strFile <> ""
[Code]....
For work I have to create copies of the same workbook for every day of the month. I have got the following to do it properly, which may not be the best way so I am open to suggestions, but I don't want it to save this macro in every workbook. There are however other macros that I do want it to take with it, just not this one.
Sub SaveByDate()
Dim a As Integer, b As Integer, c As Integer
' number of days in month + 1 (this is just a test; for Jan a = 32)
a = 6
' start date
b = 1
c = 0
For c = 1 To a
If c < a Then
MyMonth = "Jan"
MyDay = b
MyFileName = MyMonth & " " & MyDay & " 2008 daily report"
ActiveWorkbook.SaveAs Filename:=MyFileName
b = c + 1
End If
Next c
End Sub
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I have a worksheet with the following columns:
Brand
UserName
FirstName
LastName
Email
[Code]....
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
I am trying to create a timekeeping workbook that will track the time I spend on certain tasks. I have created the attached worksheet including macros that correctly allows me switch between tasks but I cannot run this set of macros in one workbook and work in another.
AtTask Timer.xlsm
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.
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
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.
The master Workbook has the following tabs:
Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)
Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)
Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)
Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).
If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"
Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.
If master workbook consist of the following....
Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,
Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,
Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,
tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689
It should output 3 workbooks by cost centre number.
One for 555, which consists of 3 tabs, travel, mobile and expenses.
A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.
A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.
The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.
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.
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 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...