Filtering data by specific value, copying data, paste in new workbook?

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
232
Hello all,
I was hoping someone might be able to help me with a project I'm working on. I tried looking for old threads I've posted regarding this, because I did figure out the code at some point, however, it's been a while since I've used the file and can no longer find it. I'm trying to create code to filter my data off of a specific value, such as Region. After filtering on Region, then filter on status, such as Open. Once that has been done, copy the filtered data on to a different workbook, and save it using a naming template that will enter in the date and Region the file pertains to. I would eventually like to create an automatic email that will grab the Regional Manager's email address and create a generic email that can be sent out to all of the Regional Managers for each specific Region. I really wish I could find the old code I wrote, because that would make this a lot easier. Any help is greatly appreciated.

D.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
232
Hello all,
I was hoping someone might be able to help me with a project I'm working on. I tried looking for old threads I've posted regarding this, because I did figure out the code at some point, however, it's been a while since I've used the file and can no longer find it. I'm trying to create code to filter my data off of a specific value, such as Region. After filtering on Region, then filter on status, such as Open. Once that has been done, copy the filtered data on to a different workbook, and save it using a naming template that will enter in the date and Region the file pertains to. I would eventually like to create an automatic email that will grab the Regional Manager's email address and create a generic email that can be sent out to all of the Regional Managers for each specific Region. I really wish I could find the old code I wrote, because that would make this a lot easier. Any help is greatly appreciated.

D.
Just so everyone knows, I'm not looking for a free ride, or code to be written for me, I found the following code snippet that shows me how to filter a specific column, however, I would like to know how I can filter multiple columns prior to copying and pasting the data on to a new workbook. The code snippet I found is:
VBA Code:
Option Explicit

Sub filter()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim rng1 As Range
Dim last As Long
Dim sht As String
Dim newBook As Excel.Workbook
Dim Workbk As Excel.Workbook

sht = "AllData"

Set Workbk = ThisWorkbook

last = Workbk.Sheets(sht).Cells(Rows.Count, "C").End(xlUp).Row

With Workbk.Sheets(AllData)
Set rng = .Range("A1:C" & last)
End With

Workbk.Sheets(AllData).Range("C1:C" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True

For Each x In Workbk.Sheets(AllData).Range([AA2], Cells(Rows.Count, "AA").End(xlUp))

With rng
.AutoFilter
.AutoFilter Field:=3, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

Set newBook = Workbooks.Add(xlWBATWorksheet)

newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
newBook.Activate
ActiveSheet.Paste
End With

newBook.SaveAs x."network path\Desktop\POC Validation Request" & Format(Now, "mm-dd-yyyy" & ".xlsx"

newBook.Close SaveChanges:=False

Next x

Workbk.Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

As always, any help is greatly appreciated.

D.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,094
Members
416,957
Latest member
Brovashift

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
Top