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

[Damian37]

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.

 

[Damian37]

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:

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.