Filter column by text, then export column to new workbook, replace searched text with new.

Hi All,

wondering if someone can help me automate a process I'm currently doing.

I have a large workbook which contains quotes, the ones that go live are invoiced by another department so i have to filter them and supply them a worksheet with the information. Currently I do this manually but would be handy if i could automate the process.

Basically what I need to do is.

filter Column "BF"( or column 58) to shows rows containing "YES" in this column.

Open a new workbook
Copy all of column B to the new workbook into column A ( there are more columns but I can edit to account for the others myself)

Replace all the "yes" in column "BF"(58) in original sheet to "completed"


Like I am able to do this manually but it takes quite a while and its a process done fairly regular.
any help would be greatly appreciated.

Regards
Leon

Sub FilterForInvoice()
'Set active worksheet names'
Dim WB1 As Workbook
Dim Quote As Worksheet
Set WB1 = Workbooks("Quote, Orderng & Invoicing Log.xlsm")
Set Quote = ActiveSheet
'Open Template'
Workbooks.Open Filename:=("K:\Shared\Orbital Print\Private\JOB SHEETs\Orbital Print to be Invoiced.xlsm")
Windows("Orbital Print to be Invoiced.xlsm").Activate
Dim WB2 As Workbook
Dim Jobs As Worksheet
Set WB2 = Workbooks("Orbital Print to be Invoiced.xlsm")
Set invoice = Sheets("Invoices")

'ShowAllRecords()prepare sheet for filter'
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

'TurnFilterOff()'
'removes AutoFilter if one exists
ActiveSheet.AutoFilterMode = False

'turnAutoFilterOn()'
'check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter

ActiveSheet.Range("$A$1:$BF$294").AutoFilter Field:=58, Criteria1:="=YES", _
Operator:=xlAnd
End If

'Filter Invoice to Yes'
ActiveSheet.Range("$A$1:$BF$294").AutoFilter Field:=58, Criteria1:="=YES", _
Operator:=xlAnd


'Open Jobsheet Template'
Workbooks.Open Filename:=("K:\Shared\Orbital Print\Private\JOB SHEETs\Blank Jobsheet.xlsm")
Windows("BLANK JOBSHEET.xlsm").Activate
Dim WB2 As Workbook
Dim Jobs As Worksheet
Set WB2 = Workbooks("BLANK JOBSHEET.xlsm")
Set Jobs = Sheets("JOB SHEET")

Workbooks("Orbital Print to be Invoiced.xlsm").Sheets("Invoices").Range("A").Value = Cells(Activecolumn.Range("C")

End Sub

I have managed to get the above working except for the copying over part something not working on that last line to copy the column over. and then all I need to do is change the entries filtered in filed 58 to Completed.

anyone able to help with this bit?

Regards
Leon