Macro Delete/Replace Worksheets

E

eric.nguyen312

Hi, I created a macro that filters data from a column into seperate
worksheets. When I enter new data into the column and run the macro
again, I get a Run Time Error '1004'. Can someone help me write a code
that either replaces or deletes the previous worksheets? Thanks.
 
E

eric.nguyen312

I created it using the Record Macro.

Sub FilterCreditTerms()

' Keyboard Shortcut: Ctrl+t

Columns("D:D").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=*%*", Operator:=xlOr, _
Criteria2:="=*cash*"
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Sales-Cad"
Sheets("Sales-Cad").Select
Sheets("Sales-Cad").Move After:=Sheets(5)
Sheets("Detail Open LC Balance By Job").Select
Application.Goto Reference:="CoDetailLCOpenBalance"
Selection.Copy
Sheets("Sales-Cad").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Detail Open LC Balance By Job").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=1, Criteria1:="=*irrevocable*",
Operator:= _
xlAnd
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Sales-Doc LCs"
Sheets("Sales-Doc LCs").Select
Application.CutCopyMode = False
Sheets("Sales-Doc LCs").Move After:=Sheets(6)
Sheets("Detail Open LC Balance By Job").Select
Application.Goto Reference:="CoDetailLCOpenBalance"
Selection.Copy
Sheets("Sales-Doc LCs").Select
ActiveSheet.Paste
Sheets("Detail Open LC Balance By Job").Select
Selection.AutoFilter Field:=1, Criteria1:="Prepayment"
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Sales-Prepays"
Sheets("Sales-Prepays").Select
Application.CutCopyMode = False
Sheets("Sales-Prepays").Move After:=Sheets(7)
Sheets("Detail Open LC Balance By Job").Select
Application.Goto Reference:="CoDetailLCOpenBalance"
Selection.Copy
Sheets("Sales-Prepays").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Detail Open LC Balance By Job").Select
Range("A1").Select
Selection.AutoFilter Field:=1, Criteria1:="=*Letter of Credit*",
Operator _
:=xlAnd
Selection.AutoFilter Field:=1, Criteria1:="Letter of Credit"
Selection.AutoFilter Field:=1, Criteria1:="Stand by letter of
credit"
Sheets.Add
Sheets("Sheet4").Select
Application.CutCopyMode = False
Sheets("Sheet4").Move After:=Sheets(8)
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Sales-Standby LC"
Sheets("Detail Open LC Balance By Job").Select
Application.Goto Reference:="CoDetailLCOpenBalance"
Selection.Copy
Sheets("Sales-Standby LC").Select
ActiveSheet.Paste
Range("C16").Select
Range("A1").Select
Sheets("Detail Open LC Balance By Job").Select
Range("D1").Select
Selection.AutoFilter Field:=1
Range("D1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
 
D

Dave Peterson

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb
 
Top