Hello Mr. Peter,
How are you? Again thanks for helping me in my previous topic.
If not too much, can I ask for your help again?
Right now, I'm using Excel 2007, and here is what I need to be done
OBJECTIVE:
TO PREPARE THE INSPECTION REPORT BASED ON THE FINISHES CHECKLISTS WHERE IN
THE ANSWER IS "N" AND COPY ALL THE INFORMATION FROM FINISHES CHECKLISTS
WORKSHEET <A5:Dnxx TO NEW WORKBOOK WITH WORKSHEET NAME AS FINISHES REPORT
EXCEPT THE COLUMN C (DELETE COLUMN C), TO SHOW THE FINISHES REPORT THEN SAVE
THE FILE AS WHATEVER FILE NAME.
INFORMATION
A1:D4 = COMMAND BUTTON
A5:D18 = REPORT INFORMATION (PROJECT NAME, ADDRESS, BLK NO, INSP NO, ETC)
C19:C20 = CRITERIA RANGE (A19=COMPLETED, A20=Y/N)
A19:D19 = HEADER (LOCATION, ITEM, COMPLETED,DEFECTS)
C20 = Y/N
A21:Dnxx = DATA FOR REPORTING
COLUMN A COLUMN B COLUMN C COLUMN D
R19 LOCATION ITEM COMPLETED DEFECTS DESCRIPTION
R20 Y / N
R21 FAMILY HALL FLOOR N F2- Consistent
colour tone ( Tonality )
R22 MASTER BATH WALL N W17 - Consistent
finished texture
R23 DINING CEILING Y
CAN ONLY PRINT THE ROWS AND COLUMNS WITH THE INFORMATION?
THANKS FOR YOUR HELP.
"Peter T" wrote:
I originally tested the code I posted in Excell 2003. I have just tried it
in Excel 2007 with the same data and the macro worked fine. But your adapted
code doesn't look right
ws2.Range("D7") = "FINDINGS"
ws2.Range("D8") = "N"
then you have
ws2.Range("A7:E115").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=ws2.Range("D7:D8"), _
Looks like you've got the Criteria range inside the filter range. Try
changing D7 & D8 outside A7:E115.
Typically, when using multiple columns as the criteria range you's place it
above the filter range. But as you have only the one column, try putting it
to the right, eg move D7 & D8 to column F.
Regards,
Peter T
"exploringmacro" wrote in message
...
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