Creating Macro To Filter And Copy Data

Jun 19, 2014

I have a spreadsheet. This has columns begining with A to S (with data in it)

It has rows beginning on row 5 and goes down to 85 but it gets bigger every day.

I want to create a macro with several phases

Phase 1 - Select the rows I specify, ie pop up box to decide on the rows
Phase 2 - Filter down and select the rows in this selection where column K has a value (unselecting the rest)
Phase 3 - Copying Columns A, B, D, E, F, K, from the selection
Phase 4 - Pasting these columns into A, B, C, D, E, F, of the sheet the macro sits on....

View 14 Replies


Creating Macro For One Blank Row Above With Copy Paste Data

Apr 25, 2014

The task I have in front of me is that I have a very large spreadsheet (28,000 +rows) that has data that was exported from a legacy system that we no longer have. It has data in columns A thru G. I must get this data prepped to have ready for importation into the new system. What I'm trying to do is copy the data in cell A and cell B, everytime the value in column D = 1, and insert one blank row above and copy the values from cell A and cell B to cell A and cell B on the newly created row.

Below is a picture of what I'm trying to capture:

sample image.png

I thought that a macro would be a fairly easy way to do this, but it's proven challenging for me to write. Here is what I attempted using the macro recorder:

[Code] ........

View 4 Replies View Related

Macro Filter Data & Copy To New Workbook

Aug 16, 2008

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.

View 9 Replies View Related

Creating Macro To Copy Time Sheets Data Of Employees Into Master Sheet

Apr 15, 2013

I need to create a macro to copy the data from time sheets of different employees into the Master sheet. ie., each employee's time sheet details in to a separate sheet. If any employee fails to provide his/her time sheet details. a mail must be sent to the respective employees regarding the Time sheet submission. I am providing the sample data of my files too.

Intentionally TS_Employee5.xlsx timesheet isn't provided. In which case, I have to send a mail to the respective employee, in order to remind him about the time sheet.

View 12 Replies View Related

Macro - Filter And Copy Associated Content?

Jun 18, 2014

I am having a spot of bother with my spreadsheet, when trying to automate some functionality. Effectively what I am trying to do is...

- With a comprehensive Project Plan press a button that extracts the information of cells that are marked as Critical.

- This information would pull through onto a separate Dashboard sheet, so that those critical items can get flagged to the Project Team.

- The data cannot be copied as a complete table, as there are various columns of data that I do not require copying.

- I have tried recording a macro with me 'filtering' the project plan for critical items and then copying that data across.

- This however only returns the cells originally marked as Critical, it does capture any changes to cells outside of the range in the code.

- In Column C of 'Project Plan' sheet, I have tasks marked as "Critical" or blank.

- I want to copy data of those 'Critical' rows of data, from Columns B,D,F,I

- This data is then to go into the 'Dashboard' sheet, in Columns B,C,E,F.

I embed the code below, from my feeble attempt:


View 12 Replies View Related

Copy / Filter Data Based On Data In Column Occuring X Times

Mar 7, 2008

I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as appears 40 times, so I want to get that data).

For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.

So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. 200, yahoo 120, etc... I need it to show me the 200 rows of emails and extract them to a new sheet so that I can then follow up with and see if they are interested in the corporate version.)

View 2 Replies View Related

Filter Data And Copy

Sep 9, 2006

=+IF( 'Bella Vida_DRH'!$E72=9,'Bella Vida_DRH'!A72,"")

I am trying to create a sheet that will search for all the rows that contain a 9 in a particular cell

then it will copy that row into a new sheet

if not =9 then it should go and check the next row etc. etc.

this formula works for one cell but doesn't goto the next cell if the value is false

I would really appreciate if some one could point me in the right direction

View 9 Replies View Related

Filter And Copy Data

Feb 19, 2007

Is there a way i can filter down data using dates and then copy to another worksheet. So lets i want copy all things with todays date on it and paste it to another worksheet.

View 2 Replies View Related

Copy And Paste From Filter Data?

Jun 9, 2014

How can we copy and paste filter

For Example I have copied table data and want to paste same date in J and K column then how can I do this ?

File is attached for ref : Copy and Paste.xls‎

Actually my Basic question is how can I copy data from filtered data and paste in visible cells only

View 8 Replies View Related

VBA Filter Data And Copy To Worksheets?

Oct 5, 2013

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] .........

View 9 Replies View Related

Filter Data On One Sheet And Copy To Another

Jul 27, 2006

Is there a way to easily filter the data according to the products' "index %" of a given month and then copying all data (including name, price, index%, and index value for the whole year) of those deviating from 100,0% to a new sheet?

I tried this myself with auto filters but the layout presented some problems. The sheet is created automatically by another programme, so the layout is what it is.

I really hope someone might have a solution for this since I need to sort through these sheets, with thousands of products, every month by hand!

View 9 Replies View Related

Auto Filter Data & Copy To Another Sheet

Jan 2, 2010

macro which autofilter data & copy to another sheet.

below mention are the steps, i dont knw how to write vb code to autofilter month. Please find sample workbook on
below mention link

1.Auto Filter Date 2(Column C)

2.Select First Month (eg.May 09)

3.Auto Filter Date 1 (Column B)

4.from, the month, which filter in Date 2 (from May 09 to Dec 09..last month of year)

5.Auto Filter Column A

6.Copy each unique value on output sheet

View 9 Replies View Related

How To Filter And Copy Data From One Sheet And Paste To New Worksheet

Jun 2, 2008

can excel do this (see the attachment pls). if possible can someone show me how to do that. i am new in excel vba.

View 9 Replies View Related

Creating An Automatic Filter

Jun 21, 2007

I am running Excel 2003.

In my Excel file, I have one worksheet with about 36 columns and 2500 rows of data.

I am looking for a way to create an automatic filter which will look at one column and filter out 13 specific phrases, placing the entire rows which contain the phrases in a new worksheet in the same file. Every row that does not contain one of the phrases I would like to place in another new worksheet in the same file.

I want to run this macro/add-in on multiple files.

View 9 Replies View Related

Excel 2007 :: Creating Order Form - Copy Entire Row If One Cell Has Data

Dec 3, 2012

I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)

I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)

Excel 2007

Item Number

0.9% NaCL 1000ml Bags

[Code] ....

I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.

View 5 Replies View Related

Creating VBA Code To Filter By Dates

Jun 3, 2012

I have a macro I am using that uses several date filters. There are filters for the previous month, previous 6 months, previous year, all dates in the future, etc.

Every month I have to edit the macro and change the dates in the macro so it remains accurate. Is there any way I can modify the macro so that the dates are stored in a cell within the workbook and not in the macro?

What I would like is to type the date ranges in a sheet within the workbook and have the macro look to those cells for the date filter instead of typing in exact date ranges within the macro. This way the actual macro code does not need to be modified every month. I would like to only have to change cells within the worksheet and not touch the macro code.

View 4 Replies View Related

Macro To Filter Out Data For Five Regions

Jun 24, 2006

I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".

The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".

I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.

The first record always begins from row 4, in all the sheets.

so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)
and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.

W2 will have the same starting range but might differ in how many rows to sum .

And so on for the 4 remaining region worksheets in the workbook.

View 9 Replies View Related

Filter Data Macro Code

Mar 30, 2008

I do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.

The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,

A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D

A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD

View 3 Replies View Related

Creating A Function Or Macro To Replace Data Between Cells.

Jun 2, 2009

I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.

What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.

Is this possible?

View 6 Replies View Related

Creating Macro To Transpose Column Data To Rows

May 22, 2012

How to create a macro to transpose rainfall data (in a column) into rows.

I have over 7200 years of data so using paste special will be very slow.

I have attached a sample of the spreadsheet below.


[Code] ..........

View 6 Replies View Related

Creating Macro To Compare Data From Separate Sheets

May 8, 2006

I am trying to use a macro to compare data from 2 sheets and spit out matching data in a third sheet. The first sheet has two columns of data. the second sheet has 4 columns of data. Only one column from sheet 1 and 1 column from sheet 2 will actually have matching data.

All other columns in sheet 1 and 2 are associated to the matching columns and must "tag along" into the 3rd sheet. I would love the third sheet to have a button that I just click and it populates the data.

The macro will be comparing between 50,000 and 500,000 items. Possibly a good book to use or even a referral for a proffesioanl or even some good heartedness,

View 5 Replies View Related

Using Macro To Operate Filter On Variable Data?

Feb 28, 2014

I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.

This may not be an actual vba issue?

The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.

View 3 Replies View Related

AdvancedFilter (macro To Filter Some Data From One Sheet To Another)

Jul 8, 2009

I'm having with creating a macro to filter some data from one sheet to another. I used the "record a macro" function to get the code I've posted below but part of the code is always changing.

In my workbook I have a main sheet called "Data" and another sheet called "Parts". The code below does what I need it to do, but the problem is that the cell range (A2:I139) is not constant and may change depending on how many rows of information I import on the "Data" sheet.

What I'd like to have happen is either have a dialog box pop up and prompt me to select the cell range (I've tried using the Application.InputBox function but can't figure it out), or since the first cell will always be A2 and the last column will always be I, have excel automatically figure out the last row that contains data.

View 2 Replies View Related

Exclude Unwanted Data After Macro Filter

Sep 9, 2006

I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.

View 9 Replies View Related

Creating Macro To Convert Formulas To Values On Data Sheet?

May 16, 2012

I am creating a Macro to convert formulas to values on a Datasheet after each entry from a form (worksheet) is carried over. Since each entry will go on a separate row I created formulas to give the new datas location. I just can't get the syntax correct for it to run.

Sub Convert_Formulas_to_Values()
Range("Reviews!$B$202").Value: Range("Reviews!$AF$202").Value.Select

View 4 Replies View Related

Creating Macro That Can Grab Varying Amounts Of Data And Put Them In New Worksheets

May 1, 2013

I have one worksheet with four columns of data. Column A is a well name, RA-0001, column B is the measured depth of the well from 0 feet to however far down it goes, anywhere from 4000 to 15000 feet, column C is the inclination of the well, column D is the Azimuth.

I have 500 wells from RA-0001 to RA_0500 or so all in this one worksheet, all the wells have varying Measured Depths associated to their well name. I need to create a macro that can separate the wells and either put them in a new worksheet for each well, ie. a worksheet named RA-0001, RA-0002, ..... ect. OR, and this would be nicer, a macro that can actually save all these individual wells as (Formatted Text (Space Delimited)) files with the associated well name.

Here is an example of what it looks like. The columns do not have a subject line to state what information is in each column because I dont need it in that format.



[Code] .......

Even just knowing how to create a simple macro that would take all the data from each well so I could manually copy and save them as new files.

View 7 Replies View Related

Creating Custom Pivot Table Report Filter

Aug 8, 2012

I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.

View 1 Replies View Related

Trying To Build A Macro Filter Based On Data In Either Of Two Columns

Jul 31, 2008

I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).

I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.

View 9 Replies View Related

Insert Cell Reference Into Macro For Data Filter

Nov 19, 2013

I want to insert a cell reference into the code below to replace the hard coded "100500" so the value in cell A1 replaces the criteria below. Is this possible ?

ActiveSheet.Range("$A$3:$B$17").AutoFilter Field:=2, Criteria1:="100500"
Range(Selection, Selection.End(xlDown)).Select

View 2 Replies View Related

VBA Macro To Pull Data From 3 Closed Workbooks And Filter

Dec 13, 2013

What I want to do is pull data from columns A,B,C,D in 3 other closed workbooks (export.csv, export1.csv, & export2.csv) to my main workbook (Thunder.xlsm)and append that data in columns A-D on my main workbook. In addition I created a list of 10 names (but need to expand or delete from this list as personnel change) in Column O that I would like to filter the data in A-D. The names in the list must find the data I want to keep in column D, the rest I want to delete (not the entire row, just A-D for that particular row that does not match the criteria in column O, and shift those cells up).

I've attached a sample spreadsheet that will clarify what I am asking. I'm preferably asking for 2 macro's, one for the pull of data, and the other for the filtering.

Column A
Column B
Column C
Column D


View 1 Replies View Related

Copyrights 2005-15, All rights reserved