Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Hi,
Not that I know of. If you have only changed 1 sheet then what problem are you trying to solve? Mike "exploringmacro" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Hello Peter T,
Thanks so much for your help. Its working now, I have another question, on the below command, is there a way to save the print area only? I mean, I want below rows with button to be hidden. Many many thanks. "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Sub test2()
Dim nLast As Long Dim rng As Range, rHide As Range Dim ws As Worksheet ActiveSheet.Copy Set ws = ActiveSheet On Error Resume Next Set rng = ws.Range(ws.PageSetup.PrintArea) On Error GoTo 0 If rng Is Nothing Then If MsgBox("Set the Print area to the Usedrange?", vbYesNo) = vbYes Then Set rng = ws.UsedRange ws.PageSetup.PrintArea = rng.Address End If End If If Not rng Is Nothing Then nLast = rng.Columns(rng.Columns.Count).Column If nLast < ws.Columns.Count Then Set rHide = rng.Offset(, nLast).Resize(, ws.Columns.Count - nLast).EntireColumn rHide.EntireColumn.Hidden = True End If nLast = rng.Rows(rng.Rows.Count).Row If nLast < ws.Rows.Count Then Set rHide = ws.Rows(nLast + 1).Resize(ws.Rows.Count - nLast) rHide.EntireRow.Hidden = True End If ws.ScrollArea = rng.Address End If If Application.Dialogs(xlDialogSaveAs).Show Then ActiveWorkbook.Close End If End Sub As written, rows/cols to top/left of the printarea will not be hidden Will look at your other question later Regards, Peter t "exploringmacro" wrote in message ... Hello Peter T, Thanks so much for your help. Its working now, I have another question, on the below command, is there a way to save the print area only? I mean, I want below rows with button to be hidden. Many many thanks. "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Hello Peter T,
Again thank you so much. You are really an expert. Its working now, as what I want, thanks so much. Hopefully you can help me again my other problem which I sent a while ago. Many many thanks. You're awesome. "Peter T" wrote: Sub test2() Dim nLast As Long Dim rng As Range, rHide As Range Dim ws As Worksheet ActiveSheet.Copy Set ws = ActiveSheet On Error Resume Next Set rng = ws.Range(ws.PageSetup.PrintArea) On Error GoTo 0 If rng Is Nothing Then If MsgBox("Set the Print area to the Usedrange?", vbYesNo) = vbYes Then Set rng = ws.UsedRange ws.PageSetup.PrintArea = rng.Address End If End If If Not rng Is Nothing Then nLast = rng.Columns(rng.Columns.Count).Column If nLast < ws.Columns.Count Then Set rHide = rng.Offset(, nLast).Resize(, ws.Columns.Count - nLast).EntireColumn rHide.EntireColumn.Hidden = True End If nLast = rng.Rows(rng.Rows.Count).Row If nLast < ws.Rows.Count Then Set rHide = ws.Rows(nLast + 1).Resize(ws.Rows.Count - nLast) rHide.EntireRow.Hidden = True End If ws.ScrollArea = rng.Address End If If Application.Dialogs(xlDialogSaveAs).Show Then ActiveWorkbook.Close End If End Sub As written, rows/cols to top/left of the printarea will not be hidden Will look at your other question later Regards, Peter t "exploringmacro" wrote in message ... Hello Peter T, Thanks so much for your help. Its working now, I have another question, on the below command, is there a way to save the print area only? I mean, I want below rows with button to be hidden. Many many thanks. "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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Hello Peter T,
Again thank you sooooo much for helping me. Its working now, I just ammend as per my data and its working perfectly fine. thank you thank you thank you thank you. God Bless :) "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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save the activeworksheet only
Hello Mr. Peter,
As per my post below. I've created a macro, but this macro only create and prompt to save the new workbook which is correct, but the thing is no information inside. For more clear explanation, this is the situation. I have excel file, Finishes Checklists.xlsx (see below), I need to generate report to new workbook and prompt to save the file. Finishes Checklists.xlsx Site Name : Microsoft Project Name : Corporation Date : 17 June 2009 Inspection No : first A-Location B-Item C-Completed D-Defects Y/N Dining Ceiling N C5-Ceiling Stain Private Lobby Wall N W1-No Painting Master Rm Floor Y Create new workbook - Finishes Report (Worksheet Name), and prompt to save as any file.xlsx Site Name : Microsoft Project Name : Corporation Date : 17 June 2009 Inspection No : first A-Location B-Item C-Defects Dining Ceiling C5-Ceiling Stain Private Lobby Wall W1-No Painting thank you. "exploringmacro" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keyboard Shortcut to toggle b/w Find and Replace and ActiveWorksheet? | Excel Discussion (Misc queries) | |||
ActiveWorksheet vs Worksheets.Item(1) | Excel Programming | |||
Copy ActiveWorksheet Columns B,C,F to Another Worksheet in Workboo | Excel Programming | |||
Disable save, save as, but allow save via command button | Excel Programming | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming |