I have data spanning many files which are named with the date on which they were created (so there are 31 files for August).
PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
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...
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
I need a macro to go into each subfolder: Open each excel workbook (which will usually contain one sheet), and insert a blank new row at the top of each sheet (without overriding existing row), filling it with headings like January, Feb....December.
I want to use a for command, but not include all the i's between two numbers. The reason is I want it to activate open workbooks that have similar names, like '110000 2007.xls', '120000 2007.xls', '130000 2007.xls', '150000 2007.xls' and so on.
I want my code to look something like this:
For i = 11, 12, 13, 15 Windows(10 * i & "000 2007.xls").Activate 'do stuff Next i
Is there a way I can do this? Or use another command?
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like. Example worksheet (Input)- Worksheet ABC Col A Col E 1/1/2004 $25 1/8/2004 $30 1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook ColA Columns B Column C Column D Sheet 1/1/2004 1/8/ 2004 1/15/2004 ABC $25 $30 $15 LMN $xxx $yyy $zzz
I'm looking for some way to open multiple protected workbooks in one time, these workbooks has been protected , but all with the same password. usually, when i open one workbook, open ,enter password, but there are so many of them that i want to open all these workbooks in the same time, the problem is each workbook showed a enter-password box, it so inefficient, i want all these protected workbooks show only one enter-password box.
I have a created a main workbook with a macro that opens workbooks on a particular sheet from a list of workbook paths and filenames in the main workbook. The macro are suppose to copy the sheet and paste it into a sheet with a new name that I have defined from the list in the main workbook. I can get the macro to open the workbooks and close them. But I can't get the macro to copy the sheet into the main workbook.
I have attached the code and the workbook. I used slet_ark to delete previous sheet and hentark and henttekstfil to open the different workbooks.
The code I use are:
Public Sub Slet_ark() Dim ark() As Variant Dim Counter As Long Dim FirstSheet As Long Dim LastSheet As Long
I'm trying to build a macro to open multiple files at one time that will always be saved in a consistent drive. The problem is that sometimes there will only be one file for a month (ie only at month-end) and other times, there may also be additional files for different dates throughout the month. I wont know ahead of time how many files there will be, but they will always be saved in the same file name type that is "FILE DESCRIPTION MM-DD-YYYY". How can I build a loop that looks for a file on each possible day but doesn't error out if the file doesn't exist?
I'd like to use VBA to open 3 workbooks in their own session/instance/etc of Excel 2007. Ideally, there will be one main workbook, that upon opening will subsequently open the other 3 workbooks.
Also, when I open that first main workbook, I will obviously have to enable macros to get any code to run. Is there a way to carry this enabling of macros over to the other sessions to avoid having to enable macros in all 3 other workbooks? (Changing my security settings isn't an option)
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example: workbook 06-01-2011 workbook 06-02-2011 all the way to workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do: ='C:File Path[WorkbookX.xls]Sheet1'!A1 where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1 where D1 is the full path name or ='C:File Path[Workbook"D1".xls]Sheet1'!A1 where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
I have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
way to suppress the code in the target workbook I open through workbooks.open
I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?
Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributorís information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.
Example: Spreadsheet one separates detailed sales information for: Abc Company 123 Company Misc. Company
Spreadsheet two separates summary sales information for: Abc Company 123 Company Misc. Company
Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.
I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. Iím hoping thereís a better way!
Iíve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.
In one workbook, i have a sheet with date like this:
In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!
I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open?
I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.
I would like to create a summary for the ordering history of each customers. The IT department will facilitate us to generate some raw data and I want to retrieve the data to the summary excel when I type the Ref No of the customer.
For example, I have the following raw data generated, in which the file name is "A123456":
Ref No Name Address
And I want to extract the data to the following summary. When I type "A123456" in the field "Ref Number" in this summary, it will automatically retrieve data from the corresponding raw file:
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList() 'The code below creates and names a new tab for each members info Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Sheet1") Set rng = Range("Database")
'extracts a list of member or distributor names ws1.Columns("R:R"). AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("T1"), Unique:=True r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r) 'adds the member name to the criteria area above ws1.Range("U2").Value = c.Value ...............