Can I save the activeworksheet only

[exploringmacro]

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

 

[Peter T]

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

Regards,
Peter T

 

[Mike H]

Hi,

Not that I know of. If you have only changed 1 sheet then what problem are
you trying to solve?

Mike

 

[exploringmacro]

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]

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]

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]

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").EntireColumn.Clear

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

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

End Sub

Regards,
Peter T

 

[exploringmacro]

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

 

[exploringmacro]

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").EntireColumn.Clear

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

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

End Sub

 

[Peter T]

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("D78"), _

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]

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 <A5nxx> 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

A14 = COMMAND BUTTON
A518 = REPORT INFORMATION (PROJECT NAME, ADDRESS, BLK NO, INSP NO, ETC)
C19:C20 = CRITERIA RANGE (A19=COMPLETED, A20=Y/N)
A1919 = HEADER (LOCATION, ITEM, COMPLETED,DEFECTS)
C20 = Y/N
A21nxx = 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.

 

[exploringmacro]

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.