Hello Peter T,

Again thank you so much for your help.

I just have one question, I'm not sure if you encounter this already.

The macro you've send me was working perfectly fine using MS Office 2003.
then I copy the file and open it using MS Office 2007, then when I run the
macro, error message comes out.

Error Message: Run-time error '1004':
The extract range has a missing or illegal field name.

Here is the macro: when I click the debug, it points to UNIQUE:=FALSE.
But when I open the same file in Office 2003 and run the macro, it works
perfectly fine, I search the microsoft office online but can't find any
answer.

Thank you so much.

Sub aDVfILTER()

Dim ws2 As Worksheet, ws3 As Worksheet

Set ws2 = Worksheets("Conquas21 QM Internal Checklist")
Set ws3 = Worksheets("Conquas21 QM Insp Report")

' criteria range
ws2.Range("D7") = "FINDINGS"
ws2.Range("D8") = "N"

ws3.Columns("A:D").EntireColumn.Clear

ws2.Range("A7:E115").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=ws2.Range("D7:D8"), _
CopyToRange:=ws3.Range("A7:E75"), _
Unique:=False

ws3.Range("D:D").Delete
ws3.Columns("A:E").EntireColumn.AutoFit

End Sub

"Peter T" wrote:

One way would be to use the Advanced filter. I adapted the following from a
recorded a macro -

Sub AdvFltrTest()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

' criteria range
ws1.Range("F1") = "FINDINGS"
ws1.Range("F2") = "N"

ws2.Columns("A:D").EntireColumn.Clear

ws1.Range("A1:D5").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=ws1.Range("F1:F2"), _
CopyToRange:=ws2.Range("A1:D1"), _
Unique:=False

ws2.Range("C:C").Delete
ws2.Columns("A:D").EntireColumn.AutoFit

End Sub

Regards,
Peter T


"exploringmacro" wrote in message
...
Hello Peter T,

Again, thanks for your help. If not too much, can I ask one more.

I have book1 with sheet1 and sheet2. The sheet1 have 4 columns C1=Code,
C2=Description, C3=Findings, C4=Remarks (this sheet1 is the list of the
checklist, where user key in either Y or N on C3), what I need is the
report
to be created in Sheet2, on this Sheet2 what I want is all the rows in
Sheet1
where C3=Y will show or appear in Sheet2 with all the columns except C3.

Is this possible? Actually its the same as lookup, where I select N on C3
then only the rows with C3=N will show, if I use this lookup, can I link
sheet1 to sheet2, so it will automatically do the lookup or any
suggestions
on this thing?

Appreciate your help... many many thanks

Example: SHEET1
C1=CODE C2=DESCRIPTION C3=FINDINGS
C4=REMARKS
W31 WALL COLOR Y
W32 WALL SIZE N
100MM
W33 DOOR TIMBER N
200MM
W34 DOOR STAIN Y



SHEET2 (CONDITION IS, WHERE SHEET1 C3=Y, THEN C1,C2,C4 WILL SHOW TO
SHEET2
C1,C2,C3)
C1=CODE C2-DESCRIPTION C3=REMARKS
W31 WALL COLOR
W34 DOOR STAIN

"Peter T" wrote:

Sub test()
ActiveSheet.Copy
If Application.Dialogs(xlDialogSaveAs).Show Then
ActiveWorkbook.Close
End If
End Sub

Regards,
Peter T


"exploringmacro" wrote in
message
...
Hi, currently I have a macro wherein it save as to new filename, I use
the
below and its working.

Application.Dialogs(xlDialogSaveAs).Show

My question is, is there a way to save only the active worksheet
instead
the
workbook?

EG. I have workbook with sheet1, sheet2 and sheet3. I made a changes to
sheet3 only, is there a way that when I click the save button, it will
only
save the sheet 3 instead the entire workbook?

Your help is greatly appreciated.

thanks