VBA To Copy Specific Columns After Filter Applied

Feb 4, 2014

Sheet one contains a table of table from C6:AY7000

On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.

On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.

So BN3 = Sheet2C3
And BN4= Sheet2C4

I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.

This is the coding I have written to select and filter the correct data values.

Sub AddFilter()
'
' AddFilter Macro
'
Dim rCrit1 As Range, rCrit2 As Range
With Application
.EnableEvents = False
.ScreenUpdating = False

[Code] .......

This now gives me the correct data in the table.

I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY

The copied data will then be pasted into sheet 2 starting in cells B9

I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.

View 2 Replies


ADVERTISEMENT

Filter, Copy Specific Columns, And Paste To Another Sheet.

Sep 27, 2009

Yeah it is 1:33 am where I am.

I'd like to filter a column and paste only 2 columns to another sheet in the first available row.
I found a code that was posted by Tom Ogilvy and made a few adjustments: ....

View 14 Replies View Related

Adding A New Row To A List When Filter Is Applied

May 14, 2009

If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.

View 2 Replies View Related

Sum Filtered Data After Filter Is Applied

Jan 20, 2009

I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?

View 9 Replies View Related

Deleting Entire Rows Once Filter Has Been Applied

Nov 24, 2011

I've got a report which is approx 40,000 rows long and I need to delete out alot of the info.

I need to delete out entire rows if column C contains a 0.

I've written a loop to do this but as theres so much data it take a long time to run.

Is there a smarter way to do this?

View 6 Replies View Related

Checkbox To Filter Columns With Specific Value?

May 4, 2014

I want a checkbox to filter column with specific value and hide others. And also there are two checkbox, one should be automaticly unchecked if we check another checkbox.

View 1 Replies View Related

Filter Columns With Specific String?

May 28, 2012

I want to filter columns from an excel spreadsheet where the value in the cells contains/includes the String "Create".

View 3 Replies View Related

Specific Find Followed By Equation Applied And Outputted To A New Column

Mar 23, 2007

I have the data sheet, which is the result of another function. It produces 10 columns with numbers in each cell, there can be any number of rows, but the key is that the rows are outlined as follows. The first row has the first column number bolded, followed by the second row not bolded and then a space and a repeat of the bold cell, followed by the non bold cell. Each row fills up all ten columns. I have attached an example of the sheet.

What I would like to do is create a function which says brings up a box that says "Input Row" and the user would input the bolded number of a row. From here the code would take that value entered and divide it by the inputted bolded number’s adjacent cell, to the right, aka B2 column. This value would then be multiplied by every other bolded rows second cell, B2 column, essentially giving rise to a normalization of that column. The output would then be displayed in a new column, column 11 in italics. Each row would now have its italics value corresponding to the bolded number’s row and not the other rows.

View 7 Replies View Related

Adding An Auto Filter On Specific Columns Of A Worksheet..?

Dec 14, 2007

How do I go about using adding an auto filter on specific columns of a worksheet..?

I.e. I want to auto filter column "D", "G" and "I" but none of the columns in-between ("E", "F" and "H")

Currently I can only create the filter for one column or a group of columns that are next to each other)

View 9 Replies View Related

{Sum(if(... )} Applied To Whole Columns

Jun 23, 2008

I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.

The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)

The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)

how to use {Sum(if(... )} to whole columns?

View 9 Replies View Related

Hide Columns, Filter & Copy Visable To New Wrkbk

Jun 4, 2007

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"

View 9 Replies View Related

Hide Columns, Filter & Copy Visible To New Workbook

Jun 4, 2007

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"

View 9 Replies View Related

Copy Row (specific Columns Only) If Row Meets Criteria

Jan 24, 2013

I have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.

Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).

Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).

VB:
Sub cond_copy()
'assuming the data is in sheet1
Sheets("Sheet1").Select
Sheet2.Range("A2:G5000").ClearContents

[Code] .....

View 1 Replies View Related

Copy Two Specific Columns From One Spreadsheet To Another In Same Workbook

May 29, 2014

I'm looking to write a macro code that copies two specific columns to a separate spreadsheet in the same workbook, and then sort the rows by values in ascending order.

View 5 Replies View Related

How To Copy Specific Columns Form One Workbook To Another

Mar 3, 2014

I have a code that copies a column data from one workbook to another workbook

[Code] .....

This copies only D column to A coulmn of another workbook

I want 2 more columns to copied and pasted in another workbook

G column---- C Column
H column---- E Column

View 1 Replies View Related

Copy Columns With Specific Text In Header Row

Apr 16, 2008

The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working.


Select Case oColHeader
Case InStr(oColHeader, "how much", 1) > 0
Selection.EntireColumn.Copy
Case InStr(oColHeader, "level of sat", 1) > 0
Selection.EntireColumn.Copy
End Select

View 9 Replies View Related

Copy Duplicate Data (paste Some Specific Columns)

Apr 18, 2014

I need copy duplicate data to another sheet, but paste only column(1,2,4,5,6,13,14)

[Code] .....

Cross-Post : [URL] .....

View 3 Replies View Related

Copy / Paste Columns That Has Specific Range With Formulas

Jan 3, 2013

Is there any way to copy a column that has a specific range

=COUNTIF(B10:BE10,"*p")
=COUNTIF(B12:BE12,"*p") etc

to copy that to a column lets say 5 columns to the right without my range changing?

=COUNTIF(G10:BJ10,"*p")
=COUNTIF(G12:BJ12,"*p")

I need the same range in all columns, and I cant seem to accomplish this.

View 3 Replies View Related

Change Table Filter Greater And Less Than To Specific Value In Specific Cell And In Another Worksheet?

Apr 16, 2014

I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.

Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select

[Code] .......

View 2 Replies View Related

Macro To Copy Data From Specific Columns And Rows To New Worksheet?

Apr 29, 2014

What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.

Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.

Requirements:

1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.

2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1

3) When column is found, data from row 23 column (x) will be copied to sheet macro1

4) repeat requirement 3 till no more Scenario's

View 14 Replies View Related

Excel 2007 :: Copy And Paste Formula To Specific Columns

Aug 27, 2012

I need to copy a specific column range K18:M180 to only columns that have an "X" on a specific row. Below is my small example.

In this example it would be in columns OPQ and WXY

Excel 2007IJKLMNOPQRSTUVWXY
121314xxxxxx1516$Units$/Units$Units$/Units$Units$/Units$Units$/Units
1718aaa180.0 0.00 19bbb0.0 0.00 20ccc1.6 0.11 14.74 21
ddd9.0 0.53 16.93 22eee7.3 0.45 16.22 232425Sheet1

View 1 Replies View Related

Filter Only Columns A-F When Apply Filter Values?

Mar 26, 2013

I have some columns on which I have a filter, with some columns next to those that have information in them.

What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..

View 1 Replies View Related

Filter Many Columns With One Dropdown Filter?

Mar 19, 2014

How do I filter multiple columns of information with the use of one dropdown arrow?

see the attached picture (jpg.) for a visual representation of exactly what I am trying to achieve. Excel Filtering.jpg

I have also attached an Excel Filtering Example spreadsheet for use in explaining how to create this type of stacking filter. Excel Filtering Example.xlsx

View 6 Replies View Related

Macro To Copy Six Specific Columns From One Worksheet To Another Worksheet

May 12, 2008

What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)

The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.

In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas

View 9 Replies View Related

Filter A Row In Specific Criteria

May 5, 2009

I would like to filter a row using the following criteria: date should be < or = todays date.

I have tried the below but it doesn't work

Selection.AutoFilter Field:=11, Criteria1:="

View 9 Replies View Related

Macro To Copy Specific Cells From Row From Source &amp; Stop When Next Row Cell = Specific Value

Nov 7, 2008

I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)

After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.

Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.

Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select

View 9 Replies View Related

Filter Specific Rows In Excel?

Jul 13, 2014

I have some data captured in a spreadsheet. The data was recorded every 15 seconds in this format 00:00:15. The total time of recording was 24 hours. I need to filter this data to every 2 minutes and capture it into another sheet.

I have attached an example of the data captured.

View 2 Replies View Related

Auto Filter To Specific Cells

May 27, 2009

I have recorded the following macros but need to fine tune them. In Field 1, I would like to be able to have criteria 1 be “=>” the value in cell “D9” instead of “160”. Likewise criteria 2 needs to be “<=” the value in cell “D10” instead of “170”. Fields 2 and 3 refer to columns “E & F” so they would be respective cell values.

View 2 Replies View Related

Select First Specific Range After Filter?

Nov 20, 2012

i just want to select first four column cells(A,B,C,D) in first row after filte the data.

View 1 Replies View Related

VBA To Capture Info Using Filter On Specific Column

Jan 30, 2014

I'm trying to add code to an existing macro that is working well to add a feature.

As it sits now, my code produces a worksheet tab called "Matrix" that has headers in row 1, a variable length list of employee names in column 1, and then a variable number columns with data crossreferenced by name in the body of the spreadsheet. Not every row/column intersection has data, some are blank if the ItemXname does not apply to that individual.

What I want to do is select all the cells on this spreadsheet, apply a filter, and then filter based on the selection of names in column 1. But I want to do this using VBA, where the macro runs, it asks the user if they want to filter on one or more names. If no, then it skips this part. But if yes, then the next thing they see is the filter window like they would see if you did this manually. After making their selections and hitting ok, the VBA routine would then take their selections (some finite number, but could be 5 -10 names) and process the rest of the macro accordingly.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved