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

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

Regards,
Peter T


"exploringmacro" <(E-Mail Removed)> wrote in message
news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
> 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

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

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" <(E-Mail Removed)> wrote in message
> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:C746953E-426E-4BA2-A085-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
>> > 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:C746953E-426E-4BA2-A085-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:0A409581-ECC8-4EE3-A761-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
>> > 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
>>
>>
>>

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").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" <(E-Mail Removed)> wrote in message
> news:0A409581-ECC8-4EE3-A761-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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" 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").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" <(E-Mail Removed)> wrote in message
> news:0A409581-ECC8-4EE3-A761-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:428F1BC0-037F-4677-8DF5-(E-Mail Removed)...
> 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" 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").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" <(E-Mail Removed)> wrote in
>> message
>> news:0A409581-ECC8-4EE3-A761-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in
>> >> message
>> >> news:E815727F-10E4-44A7-B5BA-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>