Outputting Files Based On Filters Vba
May 17, 2007
I need to be able to create a series of files based up data which is needs to be filtered.
1st of all i need to import data from another spreadsheet, so i am cprrect is assuming i can do this by using the
Applications. GetOpenFilename
method, and copying the data accross into a temp spreadsheet.
Stage 2 of this i need to extract anything which is displayed "JOB" in column D, and create a new spreadsheet called "JOB_(previousWorkingDay).xls" e.g. JOB_010507.xls
Stage 3 is the tricky part.
In column A is a currency and column D a Account.
I need to create a new file for each Account and Currency.
so if there is 1200 rows, but only 3 accounts, with 4 currencys in each, the files that output would look like this, and each file would contain that data.
for example.....
340985_GBP_01052007.xls
340985_AUD_01052007.xls
340985_USD_01052007.xls
340985_NOK_01052007.xls
445554_GBP_01052007.xls
445554_AUD_01052007.xls
445554_USD_01052007.xls
445554_NOK_01052007.xls
675567_GBP_01052007.xls
675567_AUD_01052007.xls
675567_USD_01052007.xls
675567_NOK_01052007.xls
i think i could do the filtering, but i'm a bit unsure on how to tell it to output the data into the appropriate files.
View 3 Replies
ADVERTISEMENT
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
May 26, 2006
I want the function: =VLOOKUP($A4281,$C$14:$L$720,4) to be outputted to a cell from a macro so that excel calculates it by itself, but I want the parameters to be able to change. So basically I want to build it in VB and then output it to excel to caculate. The purpose of this is so that a new user doesnt have to sift through code to see how something was calculated. ALSO, I want to be able to do teh same thing except for somethign like =C4*R5*T7
but I want all the cell names there to be able to change before I put it into any cell. So as I go through a loop I can change the parameters but still allow a new user to see how these were calcaulated by the macro.
View 2 Replies
View Related
Oct 4, 2013
I have data which could be in formats such as the following:
0.361 x 3.8
Test: 8.90x0.20=0.487
0.362 * 1.4
~0.85 (0.32*2.45)
0.362 * 1.4=0.67
It could have other similar variants. Basically, in one cell I need to output the SMALLER 0."" number. (Or if there's only one 0."", than that number). So answers from the aforementioned data would be:
0.361
0.20
0.362
0.32
0.362
In another cell, I need to output the n."" number where n is the number above 0. So would need the following answers from the data:
3.8
8.90
1.4
2.45
1.4
I've tried to use functions such as ISNUMBER(SEARCH("n.*" if n>0 or something similar but can't get my head round how to make it this specific!
View 1 Replies
View Related
Oct 16, 2013
I would like to be able to output the rows in the attached spreadsheet to separate sheets on the basis of whether they have a Y or an N in the four rightmost columns - i.e. I want to make SOLO, DUO, TRIO and FULL BAND sheets.
I would ideally like these sheets to update automatically when I change the data in the main spreadsheet.
View 3 Replies
View Related
Dec 18, 2013
I have a table like this:
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 2
I'm trying to pull each Category once out into 1 column, and each of it's Topics once in another column.
View 5 Replies
View Related
Aug 21, 2012
I'm trying to make a pivot table that can compare sales based on the whatever month/year/salesman combination I give.
Ideally, I'll be comparing the sales data for 3 seperate months. Can I make a pivot table where I can make different filters apply to specific columns?
View 1 Replies
View Related
Jul 1, 2014
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies
View Related
Aug 5, 2013
I have a table which says that this is the amount of coloured cells we have in another sheet.
For e.g.
Field Name Code A
Item Description 5
Now, the item description column has 5 cells in another sheet which are filled in with "Yellow" Colour. So what i want to do is to click on this 5 in sheet 2 in this case as per the attached sample which takes me to the filtered result on sheet1 of 5 yellow coloured cells under the column of Item description including an additional filter of Code "A"
View 1 Replies
View Related
May 26, 2013
I have several .mp3 files in a directory that I'd like to rename based on a list.
Currently I have a sheet with two columns starting at "A2" and "B2"
Column A contains the file path and old name (without extension) ie: C:RenameFromListOldName_01
Column B contains the file path and new name (without extension) ie: C:RenameFromListNewName_01
I've modified the code below (obtained here on these forums) to work with mp3 files, which renames them, but it also corrupts my files.
VB:
Sub Rename_Files_from_List()
Dim oFiles As Range, fPath As String
Set oFiles = Range("A2", Range("A65536").End(xlUp))
fPath = Range("D2").Value
[Code] .....
View 4 Replies
View Related
Jun 11, 2013
In every month I have to send report to each sales person provided one sales person should not get info of another sales person. Now I am doing it by making file after sort using copy and paste which is time consuming. Is there any way that I can make individual file for each sales person in shortcut way.
View 1 Replies
View Related
Nov 10, 2008
I have multiple files in one directory. I have a master sheet that gives me basic information about all those files. In column K it list's all the file names and file paths. I would like to be able to use the auto filter to sort these files. Once the files are sorted they are opened and the relevant information (the CHRD page) is then copied into the master (Query.xls) the first copied page is renamed to sheet1 and second to sheet2 etc. Right now i have to manually run the macro's depending on which line the files are listed. I have also had to create separate macro's for each line.
View 2 Replies
View Related
Sep 30, 2011
I'm a long time excel user and I've come up with an issue, it can surely be solved in any other larger database language but excel is just my everyday friend and I was wondering if it's possible to do such a thing.
I have a list of "people" in my 1st ssheet and they have
ID | ProductName| Category | Year |
and I have this data on the 2nd ssheet
ID | Price | Description
There is 600.000 records in the primary sheet but only 22.000 in the secondary because the data doesn't repeat (one description can fit to 10.000 products, no need to write it down that much times).
But now I need to compile one big/large file, so I need to have one file that contains all:
ID | ProductName| Category | Year | + | Price | Description
In MS SQL I would use the "Where" function and compare the ID here and there, but is there a possible way to do it in excel?
View 6 Replies
View Related
Apr 17, 2014
I'm looking for how to open multiple files base on the value of Range("A1:A" & Lr).
Below is one I'm using for ForlderPicker but I'd like to select a certains file I want to
[Code] ........
View 10 Replies
View Related
Feb 27, 2013
I have seen this function Quickly split data into multiple worksheets based on selected column in Excel when searching for a solution. I would very much like to use VBA to filter data and open up new files in a similar way.
View 8 Replies
View Related
Jul 28, 2014
I have a excel file where I have the list of File Path in Column A & list of File names in Column B, Default path location is available in Column C. Now I want create a zipped folder based on the file name in Column B to the location Column C. I had browsed in Internet and found some code for creating the zipped file by selecting the files in Windows browser window. However my case is need to create the Zipped file based on excel cell contents.
I had found the code in the following location
Zip file(s) with the default Windows zip program (VBA)
create a code for creating the zipped folder based on Excel cell contents
View 2 Replies
View Related
Nov 10, 2006
I regularly receive many files that require renaming. Column A contains the current name and column B contains the desired name. Could anyone tell me how to use these lists to rename the files?
View 4 Replies
View Related
Nov 29, 2006
I have code that takes full cell with filename, looking for it in certain directory and copying to another directory.
Sheets("Unique").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
If rng.Offset(0, 4).Value <> "" And InStr(1, rng.Offset(0, 4).Value, "http") = 0 Then
FileCopy SourcePath & rng.Offset(0, 4).Value, ThisWorkbook.Path & "" & DestPath & "" & rng.Offset(0, 4).Value
End If
Next rng
I want to change the code that it will copy based on number. Any suggestions?
Example:
I have range of filenames.
10989478.MEC0388A-L0_RoHS_globemotors_1.pdf
10989495.Keystone_M1.4cat. zip
10989559.MEC0388A-L0_RoHS_qualtek.pdf
I want to copy files starting with 10989478, 10989495, 10989559. I mean ignore the part after "dot".
View 9 Replies
View Related
Jul 31, 2014
I'm currently creating E-mails containing a table of material numbers in the body of the message. The E-mail is generated with a macro from a table in excel. In some folder - say on the desktop - I have PDF files for each material Number. Each file is called 0012345_Product-Name.pdf where 0012345 is the material number (always 7 digits). I want the macro to look for the pdf for each material number found in the table and add it to the E-mail as an attachment. With constant, known file names this wouldn't be much of an issue. However, I'm having trouble getting the macro to only search for the first 7 digits of the filename and select it based on that.
Since we are talking up to several 100 PDF's, it would also be great to zip them, once selected - if at all possible, before attaching them to the E-mail
View 2 Replies
View Related
Feb 14, 2014
I am trying to an excel macro that will automatically import data from specific files. The basic information is this:
1. There is a MySQL script that runs every day and creates tab-delimited text file named as the date.Example:
OutputDIR
02-14-2014.txt
02-15-2014.txt
02-16-2014.txt
2. I need grab data from each of the files for a rolling 30 day period from the date specified in the sheet. The data needs to go onto the same sheet in Excel.Example: B2 on the "Settings" worksheet says 02-14-2014. So take data from 02-14-2014 minus 30 days (01-15-2014.txt) all the way up to today and consolidate it onto an existing worksheet called "Data."
3. If the date in cell B2 is changed, overwrite the data that was already retrieved and replace it with the new import from the new date.Example: B2 is changed to 01-31-2014. Now take data from 01-31-2014 minus 30 days and overwrite what was already imported in worksheet "Data."
View 5 Replies
View Related
May 6, 2013
I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.
File1.xlsx
File2.xlsx
File3.xlsx
If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx"
If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx"
If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"
If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.
View 3 Replies
View Related
Oct 3, 2013
I am trying to merge 2 files (FileA and FileB) into a new file which will be automatically named based on the date. I would only need Sheet 2 of both files to be copied over and merged into the new file. The range to be copied is from Row 3 onwards to whichever point where the data ends. All the files can be found in a specific drive and the new file should be created there as well.
View 4 Replies
View Related
Mar 25, 2014
I have a macro attached with attached file; it will list the files then will re-name the files based on other cell
example;
Cell A2 = New.mp4
Cell B2 = ggffrr.mp4
so the file New.mp4 will be renamed to ggffrr.mp4.
it is working fine but the location will be always desktop or documents location
i need from the macro to rename the files and to keep them in the same location.
View 1 Replies
View Related
Aug 4, 2006
I am about to write a macro and I want it to identify, from the C: drive, any .csv files that were created / saved today. I have managed a pure list before and I have managed to distinguish by filename, but selection by date would be exctremely useful.
View 2 Replies
View Related
May 13, 2007
I am not so sure this can be done through a macro, but here is what I want.
I have a folder with say around 1000 excel workbooks. I need a macro to
1. Open each file in the directory specified
2. Read the contents of a particular cell, for ex, A1
3. Create a folder in the same directory with the value of A1
4. And, move the excel workbook itself to that folder.
5. Then it opens the next file
6. If the value of A1 is the same as that in the first workbook, it simply moves it to the already created folder, else it creates another folder with the new value of A1 and moves it there.
Hope I am not too confusing. Searching the forums does not give me an exact picture of how it can be done.
Note: I would also like an option where when I execute the macro, it should pop up with an option to select the folder in which the files are, since the files are not always saved in a static file path.
View 6 Replies
View Related
Mar 11, 2008
I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.
View 6 Replies
View Related
Jun 11, 2008
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
View 3 Replies
View Related
Mar 10, 2013
This is what I am trying to achieve:
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail):
VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....
I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
View 3 Replies
View Related
Aug 15, 2014
All my files is in C:UsersmarrecoDownloads All files has name formatt (yyyymmdd) -> 20140814 I need import only last month files
Every day a system plays text files within the (C: Users drake Downloads ) folder.
E.g. I have to import only the files that have the name (yyyymmaa) based on last month.
[Code] ......
View 14 Replies
View Related
Mar 11, 2014
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
View 2 Replies
View Related
