I have been trying to use a FOR EACH C ...... NEXT loop, where each cell in the range is the beginning of a worksheet name that I need to import.
What I have got so far is:
Sub FindMyFiles()
Dim fs As FileSearch
Dim ImportWB As Workbook
Dim MasterWB As Workbook
Set fs = Application.FileSearch
Set MasterWB = ThisWorkbook
I have a list of filenames in Sheet1, range A1:A20. These files can be found in URL
http://mysite/x/y. Where y is the filename (including the extension) and x is the 1st 4 characters of the filename. So if in A1 I have the filename AB1234.pdf, this can be found in the link: http://mysite/AB12/AB1234.pdf. Is there a was in VBA where I can loop thru the list in A1:A20, and copy each from their respective URLs and save into C:Documents and Settings ?
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]....
I am a complete novice with VBA and would like to write a macro that will allow me to import multiple text files stored in a folder on my directory into a single excel worksheet. I would also like the name of each text file to be output in a separate column for each row of data in the worksheet i.e. each row is 'named' with the title of the file it comes from.
The text files are comma delimited and have 5 fields e.g.
.000071 ,93018.3,53.3583924, -6.3578328,Differential
In one workbook, i have a sheet with date like this:
TITLE
Data......
TITLE2
Data2....
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 use Excel 2003 & I am trying to design a worksheet to keep track of signs I make & their order numbers. I want the names listed alphabetically. When I add a new name & I arrange the column alphabetically, the columns with the order numbers do not relocate along with the names column.
View 5 Replies View RelatedI have data spanning many files which are named with the date on which they were created (so there are 31 files for August).
For example:
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...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
I have a workbook that contains worksheets. They are listed as follows:
Sheet 1ABCDEFG
In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.
For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed
Blue
Purple
Black
White
Yellow
Orange
Green
Gray
Brown
One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time
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.
I'm using Excel 2010.
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
rearrange this macro for me that I could import multiple txt (200 files) files from selected folder. Could You insert appropriate comments in Your altered macro
- where I should change how many columns I need to import from files,
- which column/columns I want to import from files
- macro should automatically import data from the whole of the selected column or columns of each text file into one single spread sheet and import it to different columns
I have found this:
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '
I've got a single folder for the year 2009 that contains multiple files, identical in nature but updated for each business-day. In other words, the same report is generated daily with updated info. The naming convention is the same for every file (i.e. Daily Net Debt Report 02.2.2009.xls)
My ultimate goal is to have a macro that is dynamic enough that if its run on any given day of the year, it pulls the data (specifically from the worksheet "Detailed Cash" cells C1:E26) from every file included in the folder and places it in a single worksheet in the master file. So, for example, data from 1/2/09 would go into cells A1:C26, data from 1/3/09 would go into cells D1:F26, etc...
I am attempting to create a macro that will loop through a range, each cell of which contains a worksheet name which needs to be activated in turn. My limited knowledge says that either of the two techniques below should work, but they do not.
Sub SelectSheet()
For i = 2 To 50
Sheets(Range(("J" & i))).Select
Next
End Sub
OR
Sub SelectSheetv2()
For i = 2 To 50
Sheets((Cells(i, 10))).Select
Next
End Sub
I understand how to return the address of a particular named range (that I already know the name of). For instance, the following works just fine for me:
MsgBox Range("BreedTestRangeName1").Address
In my current workbook, this returns a messagebox that reads: $C$13. However, cell C13 also has other range-names. Indeed, in my current program, it might have five or six more range names assigned to it. But I do not know how to return these names (if they exist), using only code. That is, I do not know how I can return those names, strictly within VBA. I have tried this
MsgBox Range("C13").Name.Name
But, of course, this only lists one range name; and it looks like it only lists the first one that was assigned to that cell. How can I return the others? If I can return them, I would prefer to avoid printing them anywhere onto a spreadsheet.
In a folder I have a group of approx 30 workbooks which each contain up to 200 worksheets. All the worksheets contain a set of formulas in the range FR1:FT30. I now need to update these formulas.
I have a precedent file called prec.xls & i need to copy the range FR1:FT30 from this file to all the worksheets in workbooks i have described above.
Currently i am looking for a macro to combine information in different workbooks but on the same sheetname. In every single workbook i have a worksheet with a single row of information which has to be added to one big file. In stead of doing all 200 manually i would like vba to select this particular range, copy it and paste them under eachother in one single worksheet in a different workbook. Is this possible?
for examble book 1, sheet 1 has all the data in rang a1 till z1. this has to be copied to the masterfile under a1. this goes on from book 1 till book 200.
How to get a formula that will substitute every X with the actual type of meat. For instance, under Monday column, my table should list Liver, Pork, Mutton and Lamb Stew instead of X.
Mon
Tues
Wed
Thurs
Friday
Liver
X
X
[Code] ........
I have attached an excel sheet which i wanted to write macro as an attachment. .
when I run a macro an email has to be sent to each persons listed in column A with Voucher # in the subject line and URL in the body of the message after the URL, the body of the message will remain same for all the emails:
so I have typed the body of the message in 2nd sheet:
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
I am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
I have a problem with a sumproduct formula. The formula works however both workbooks have to be opened at the same time or it will not update and you get a #VALUE! error. The formula is:
=SUMPRODUCT(SUMIF([Mthlydta.xlsx]FEB!$DZ$11:$DZ$65536,Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$65536))
I have also tried using
=SUMPRODUCT(INDEX([Mthlydta.xlsx]FEB!$DZ$11:$DZ$3000,MATCH(Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$3000,0),1))
But it only finds the first value in the list Cash. Which the list of accounts for cash will grow because we are expanding and have several mergers going on.
I have an email list that I email. Sometimes I get some that doesnt exist anymore. So my program sends email out and respnods to me that says these emails do not exist and gives me the emails. So I manually have to go and find them one at a time on my list to delete by CTRL F. Is there a way to submit all email names to find at once instead of doing one at a time.
View 9 Replies View RelatedI have multiple excel files from which I would like to copy specific ranges to a master file. The row should add up to the previous rows. From this side I have no problems things work fine. The problem is to copy the header which remains the same data to the top of the master file. Currently the macro I use overwrite the first line of the master file. So I loose the date from one of the excel files.
View 3 Replies View RelatedI have the following code that works properly, Copy & Paste Same Range On Protected Sheet From Multiple Workbooks
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook, book1R As Range
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit...............
I have many names of people in column A .. what formula do i use to count all the names in the range in coumn A?
View 2 Replies View RelatedIn column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies View RelatedI 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.
That does allow me to filter the output in Master, but ideally what I would want to do is not have to set aside 300 lines in the Master file for each of the Staff files. Conceptually, I'd like to have the spreadsheets Staff A, Staff B, etc. look for and export only lines which have data in them and then have Master bring those lines in automatically. This may not be possible, in which case your suggested solution is the best approach.
View 9 Replies View Related
