Macro to filter (extract) data into different worksheets based on Status on Column G in a new workbook. The new workbook can be saved in the same folder where the existing one is saved.
I have attached the Rawdata file and the sample file for reference..
I want to filter for high value items in Sheet Raw Data which meet the below criteria, if criteria met then copy data to the criteria worksheets
High value criteria
>=1,000,000 =1M=5,000,000 =5M=10,000,000 then copy to sheet >=10M
See below example
RawData  ABCDEFGHIJKL1GroupCategorySet IDValue DateEntry DateTypeAmountAUD EquivalentCCYAgeSourceRef12TESTTESTTEST21-Jan-1223-Jan-12LCR1,000,000.001,000,000.00AUD2TESTTEST3TESTTESTTEST24-Jan-1224-Jan-12LCR-3,500,000.00-3,500,000.00AUD1TESTTEST4TESTTESTTEST23-Jan-1223-Jan-12SDR5,600,000.005,600,000.00AUD2TESTTEST5TESTTESTTEST24-Jan-1224-Jan-12SDR-
[Code] .........
I would like to use a dropdown box for all the names in my worksheet. Once a name is selected have all the rows filtered on that name and copied to a new workbook.
I think I'm making this harder then it is, but I'm just not comprehending what needs to be done.
I have an excel file with data in it.I also have designed an user form so that the user can select few options in that to filter the excel file and get a new file. The reason i am doing this is because the people who are going to use this do not have any knowledge of excel. In the sample attached, the tab "Data" has the data in it. The User form should filter this particular sheet and throw out a new file. In the userform, I have mentioned the fields to be filtered.
View 2 Replies View RelatedI have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
Using Office 2011 for mac, but felt this question was best suited for general.
Here is the situation.
I have a workbook with three sheets. It's a price/invoice/labor workbook.
Sheet 1 has all the data, including my cost and loss/gain fields
Sheet 2 has labor costs and the totals are linked to Sheet1
Sheet 3 is the customer copy of the first sheet. It does not include wholesale and profit info.
I'm trying to link the cells so that when info changes in Sheet 1, it's reflected, in real time on Sheet3
Right now I'm using a formula per cell ='Sheet1'!XXX (where XXX = the cell/column location, ie D15). The first column I started has this formula in every cell, about 100 in all, copied by hand and changed to reflect. It works, but it seems like there has to be an easier way to mirror or link the data. I tried pasting special with links, but continue to get errors as the link pather includes the entire file data path from the local machine.
I still have about 200 cells left and I'm dreading doing this all by hand. Also, If I add a new row to sheet1, this will not reflect in sheet 3.
I have a report(Input report) with multiple sheets with different worksheet names.
Now I have to select 2nd worksheet of input report and copy a certain range values and paste in the other workbook(output) . From the same sheet 3 diffrent values I will copy and paste in other work book.
Now, I have to come back to the input report and go to next sheet and copy the same range data and append to the out put report below the first sheet.
This I need to do for all worksheets for the input report worksheets (there are more than 100 worksheets)
[code here]
VB:
Sub testtest()
'
'
Dim ws As Worksheet
[Code].....
I have a userform which loads the data into a worksheet named "Data" which is being used as a database for an event scheduler. I also load a calendar from a worksheet named "Month" so I need the information from the userform "UForm01" added to 2 worksheets.
The userform adds a series of textboxes to worksheet "Data" in the first open row. I also need certain textboxes from the Userform to be added to another worksheet to populate the calendar.
See the code below.
[Code] .....
I have a master workbook with 20 worksheets. 5 worksheets in the worksbook are distributed to field reps, but the remaining 15 worksheets are not. What is the best practice for receiving the 5-worksheet workbook from the field rep and importing the data into the 20-worksheet master workbook? If I merely use Copy Sheet I am able to copy the worksheets into the master workbook but then I have to remove the pathname references in each of the copied worksheets in order to map the data in the master workbook - which is quite cumbersome.
View 9 Replies View RelatedI have a weekly report which lists items and their available date. This list is always changing - new items added, old items removed. These items are required for 4 different projects and the item listed says which project the item date is for. There is also the same item across different projects which also have different dates which also appear and disappear each week depending on requirements.
I want to be able to capture the date for each of the items per project per week. Ultimately, I want to be able to track the movement of the date per item per project. I have attached a sample of how I want it to do.
I have to input information into a master worksheet which contains data (employee names, location, start date and client) that then needs to be repeated in several other sheets. I want to input the data into a master worksheet and then automatically populate the other sheets with the data. If the data could then be filterable on each sheet that would be good.
View 9 Replies View RelatedI get 'x' number of workbooks(with one sheet only) everyweek from which I need to copy data and paste to a master worksheet. (SCREEN CAPTURE BELOW)
I am unable to write the code myself as I have never worked on VBA and am only a beginner.
Part I:
The data I need to copy starts from the 19th row (A19:H19). The end point is determined by the row just before the row that has the words "Calibration Request" in it.
Part II:
Just below the data that was pasted from Part I, the data from 2 rows below the words "Calibration Request" needs to be pasted. The end point for this would be a blank row encounter.
Also some of the rows and columns are merged.
I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:
View 2 Replies View RelatedI am using code to filter my 4 sheets Greater then 0 (zero)
After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:
for 1st sheet with the name ("V2")
for 2nd sheet with the name("LV")
For 3rd sheet with the name ("F2")
and 4th sheet with the name("L2")
If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well
But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.
So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I have a spreadsheet with a single worksheet. This worksheet contains 1200+ rows of equipment. Each row of equipment is made up of 4 columns: the equipment name, then the department (made up of sections), section (made up of teams) and team (a few workers) responsible for maintaining it.
I have been given the task of making it easy for the departments, sections and teams to identify which pieces of equipment are theirs. I have been specifically told to add a new worksheet to the spreadsheet that contains 3 drop-down menus; you guessed it, department, section and team. The idea is that someone chooses the department, section and team that they work for, then click worksheet 2 and their equipment has been filtered for them. (Some teams come under more than one section or department, depending on the task so I do need all three drop down lists). What I can't do is get a the drop down lists in worksheet 1 to filter the data in worksheet 2.
I'm trying to build is a master Excel sheet for a company-wide budget tracker, where our supply person enters in information on individual orders. Those orders would be broken down in separate worksheets based upon department.
So, let's say you have three departments. Each department has an identifier code (Human Resources would have HR, Operations would have OP, and Research & Development would have RD.) Each order number is prefixed with the department's code, then the other columns deal with dates ordered and received, cost of the order, and any notes on the order.
What I'd like to be able to do is to have each order be automatically filtered out to secondary worksheets, based upon which department the order belonged to.
I have seen a macro that allowed one to push a button and break data out like that, but the problem is that it created new worksheets each time, and I want the department worksheets to stay the same (since each of those can expect to have starting budget figures updated by the supply officer.)
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.
MS VB Script error:
Runtime error '9':
Subscript out of range
Any advise would be greatly appreciated.
Thank you
Code is below..
Sub saveall()
'
'
For Each ws In ActiveWorkbook.Worksheets
ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Next ws
End Sub
I created an advanced filter macro which analyzes the "Orig_Appmt_Data" dataset from the "Summary" worksheet and provides the resulting analysis on the "Effective Rate" worksheet. However, when I change the name of the workbook, the macro fails. I was hoping someone could provide some assistance in helping adjust the vba code to take into account the subsequent changes in workbook filename. Here is the
Sub ER_Analysis()
'
Range("B8:R109").Select
Selection.ClearContents
Columns("P:R").Select
Selection.EntireColumn.Hidden = False
Range("Q5").Select
Range(" Conversion 3-28-07.xls'!Orig_Appmt_Data"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Q5:Q6"), _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R8").Select
Selection.AutoFilter
Range("B8:R87").Sort Key1:=Range("P8"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:="<>", Criteria2:="<> TOTAL"
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.Font.Bold = True
Range("P8").Select
End Sub
I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.
View 2 Replies View RelatedCan you use or apply advanced filters in a shared workbook?
I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).
I have the following Pivot Table:
Business DeveloperDonna HoffmanCount of Activity CountDate Client NameContact NameActivity Name3/5/20123/6/20123/7/2012Grand TotalxxxxxxxxxxxxxD'Agostino, Esq., Michael C.Email Sent (CLIENT)11xxxxxxxxxxxxxxxx11xxxxxxxxxxxxxxMartin, DavidEmail Sent (CLIENT)11xxxxxxxxxxxxxxxxxx11xxxxxxxxxxxxxxxxxxYoder, MichaelEmail Sent
[Code] .......
I would like to filter by Business Developer and copy and paste all text to a new workbook. I would need to repeat the same code for all Business Developers and the number of columns will vary.
This is the code I have so far. I get an error msg when it tries to paste the data to the new workbook.
Code:
Macro5 Macro
' Macro recorded 3/14/2012 by MPS Group
'
'
ActiveSheet.PivotTables("PivotTable2").PivotFields("Business Developer"). _
CurrentPage = "Donna Hoffman"
Columns("A:L").Select
[Code] .........
I have created a post on TeachExcel.com, but haven't received an answer yet:
http://www.teachexcel.com/forum/view...7d85cfb7d#p203. I've attached an example of the spreadsheet.
Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...
View 3 Replies View RelatedI need to filter data based on criteria based on user input form that is in another workbook. For example, I have a list of departments, I want to create a userform for the user to enter department number and then have excel filter only the data belonging to that department. The person will then update the department records and save the file.
Here is partial code I have been working on but having difficulties:
Dim FilterCriteria
Dim CurrentFileName As String
'Get the current file's name
CurrentFileName = ActiveWorkbook.Name 'how do I call another workbook?
[Code]...
I have an Excel sheet with all client information...For clinical supervision I want my team members to open their own "Supervision.xls" and click a button...this button will open "Client.xls" and select "Client info sheet"...It will then hide rows c,d,g & f...it will then filter column "e" based on the specific caseworkers name ( say "Joe") and copy only the visable columns back to their "supervision.xls" Values only (doesn't effect the colour formating of Supervision.xls) then close "Client.xls"
I have 3 worksheets in one workbook. I need to extract 10 random rows of information from about 193 rows in sheet 1, and post it in sheet 2 and I can't seem to find a way to make this happen correctly.
View 9 Replies View RelatedI need to count the frequency/occurance of strings "Manager", "Clerk", "HR" etc. that occur in the exact same column in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query but the formula is getting bigger if there are number of worksheets.
simplyfing the formula. Also is there anyway to put the formula on the column instead of row wise. I can use a single formula on the column instead on every row in Adminsheet.
To be clear my requirement is to count the occurrence of a search strings listed in Adminsheet column A across all worksheets and display count in Adminsheet column B
