Hello Mr. Bernie,

Below is the macro that I've created, can you please check what went wrong.
What happen is, it only open a new workbook which is correct, but nothing
inside. What I need is to copy all information from Finishes Checklists where
in the Column C = N, except Column C and except A1:D4 (dont require in the
Finishes Report).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.