Using advanced filter, Copy to New Sheet, need to add one more column of data

suzyquzee

New Member
Joined
Aug 30, 2011
Messages
6
Please see my code for my advanced filter macro. I need to add Column D to the Range of data to be copied but cant seem to do that easily. It might that I am just not very experienced in Excel and I am overlooking something or it might be that I need to do this another way. The filter to new sheet works great with the exception that I need to include the data in Column D on that new sheet.

Thanks in advance for any advice.

Code:
Sheets("Project Summary - Template").Range("AE16:AJ21").AdvancedFilter Action _
        :=xlFilterCopy, CriteriaRange:=Sheets("Project Summary - Template").Range( _
        "CU16:CW19"), CopyToRange:=Range("A16"), Unique:=False
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi suzyquzee,

You'll need to:
1. Extend the range being filtered to include Column D
2. Add the header that is in D16 to the criteria range (let's say CX16)
3. Extend the criteria range to include the added column

The revised code might look like this...
Code:
With Sheets("Project Summary - Template")
    .Range("D16:AJ21").AdvancedFilter _ 
        Action:=xlFilterCopy, _
        CriteriaRange:=.Range( "CU16:CX19"), _
        CopyToRange:=Range("A16"), _
        Unique:=False
end With
 
Upvote 0
Thank you for your reply. I adding in the D16 to the range (see below). It copys over Columns D16 thru AJ21. What I am really need on this new spreadsheet where the filtered data is copied are the data from Columns D, and then AE thru AJ. The code in my initial post brings over the desired filtered data in columns AE thru AJ, but I also need column D data to be included. is there a way to do this?

thanks

Code:
 Sheets("Project Summary - Template").Range("D16:AJ21").AdvancedFilter Action _
        :=xlFilterCopy, CriteriaRange:=Sheets("Project Summary - Template").Range( _
        "CU16:CX19"), CopyToRange:=Range("A16"), Unique:=False
 
Upvote 0
Oh I think I got it working now. The problem with the sheet I am trying to filter and copy is the row headings encompass rows 15 and 16 and there are merged cells included. If I delete row 15, which includes those merged cells, leaving only row 16 column headings then it seems to work.

thanks
 
Upvote 0

Forum statistics

Threads
1,226,497
Messages
6,191,372
Members
453,655
Latest member
lasvegasbuffet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top