Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to create a macro to generate report using advanced filter
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How to create a macro to generate report using advanced filter
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default How to create a macro to generate report using advanced filter
exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How to create a macro to generate report using advanced filter
Hi Bernie,

Thanks for your reply.

My data didnt start from A1. Below is the info.

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

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.


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.



"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How to create a macro to generate report using advanced filter
Hello Mr. Bernie,

I've created the macro as per below.

What happen is, this macro only open and prompt to save the new workbook
which is correct, but on the workbook no information inside.

What I need is, to copy and filter the worksheet based on the criteria given
from Finishes Checklists then paste to new workbook and prompt to save the
file, but delete Finishes Checklists Column C (which is the criteria range)
and A1:D4 (which is the cell for macro button).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"exploringmacro" wrote:

Hi Bernie,

Thanks for your reply.

My data didnt start from A1. Below is the info.

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

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.


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.



"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How to create a macro to generate report using advanced filter
Hello Mr. Bernie,

Below is the macro that I've created, can you please check what went wrong.
What happen is, it only open a new workbook which is correct, but nothing
inside. What I need is to copy all information from Finishes Checklists where
in the Column C = N, except Column C and except A1:D4 (dont require in the
Finishes Report).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default How to create a macro to generate report using advanced filter
Try this version.

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro2()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A19:D" & myS.Cells(Rows.Count, 4).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N"
myS.Cells.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
Intersect(myC.Range("19:" & Rows.Count), myC.Columns(3)).Delete Shift:=xlToLeft
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub




"exploringmacro" wrote in message
...
Hello Mr. Bernie,

Below is the macro that I've created, can you please check what went wrong.
What happen is, it only open a new workbook which is correct, but nothing
inside. What I need is to copy all information from Finishes Checklists where
in the Column C = N, except Column C and except A1:D4 (dont require in the
Finishes Report).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How to create a macro to generate report using advanced filter
Hello Mr. Bernie,

Thanks a lot, its working.

Only thing is, on the Finishes Report even the formula was copied over, as
this is the report editing is not allowed.

please help. thanks.

"Bernie Deitrick" wrote:

Try this version.

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro2()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A19:D" & myS.Cells(Rows.Count, 4).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N"
myS.Cells.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
Intersect(myC.Range("19:" & Rows.Count), myC.Columns(3)).Delete Shift:=xlToLeft
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub




"exploringmacro" wrote in message
...
Hello Mr. Bernie,

Below is the macro that I've created, can you please check what went wrong.
What happen is, it only open a new workbook which is correct, but nothing
inside. What I need is to copy all information from Finishes Checklists where
in the Column C = N, except Column C and except A1:D4 (dont require in the
Finishes Report).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.







Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default How to create a macro to generate report using advanced filter
Change the one line

myS.Cells.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")

to three lines

myS.Cells.SpecialCells(xlCellTypeVisible).Copy
myC.Range("A1").PasteSpecial xlPasteValues
myC.Range("A1").PasteSpecial xlPasteFormats

HTH,
Bernie
MS Excel MVP


"exploringmacro" wrote in message
...
Hello Mr. Bernie,

Thanks a lot, its working.

Only thing is, on the Finishes Report even the formula was copied over, as
this is the report editing is not allowed.

please help. thanks.

"Bernie Deitrick" wrote:

Try this version.

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro2()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A19:D" & myS.Cells(Rows.Count, 4).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N"
myS.Cells.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
Intersect(myC.Range("19:" & Rows.Count), myC.Columns(3)).Delete Shift:=xlToLeft
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub




"exploringmacro" wrote in message
...
Hello Mr. Bernie,

Below is the macro that I've created, can you please check what went wrong.
What happen is, it only open a new workbook which is correct, but nothing
inside. What I need is to copy all information from Finishes Checklists where
in the Column C = N, except Column C and except A1:D4 (dont require in the
Finishes Report).

Sub GenFinReport()

Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

'criteria range'
Set myR = myS.Range("D19") = "Completed"
Set myR = myS.Range("D20") = "N"

myS.Range("A1:E194").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=myS.Range("D19:D20"), _
CopyToRange:=myC.Range("A1:E194"), _
Unique:=False

myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1:E194")
myS.ShowAllData
myC.Columns("D").Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")

End Sub


"Bernie Deitrick" wrote:

exploringmacro,

I have assumed that your data table starts in cell A1 of sheet "Finishes Checklists" and is
contiguous (no completely blank rows or columns)

HTH,
Bernie
MS Excel MVP

Sub MacroForExploringMacro()
Dim myS As Worksheet
Dim myC As Worksheet
Dim myR As Range

Set myS = Worksheets("Finishes Checklists")

On Error Resume Next
Worksheets("Finishes Report").Delete

Set myC = Sheets.Add(Type:="Worksheet")
myC.Name = "Finishes Report"

Set myR = myS.Range("A1").CurrentRegion
myR.AutoFilter Field:=3, Criteria1:="N"
myR.SpecialCells(xlCellTypeVisible).Copy myC.Range("A1")
myS.ShowAllData
myC.Columns(3).Delete
myC.Move

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report - items to be completed.xls")
End Sub

"exploringmacro" wrote in message
...
Hello,

Can somebody help me.

I'm creating a macro, wherein, I have a workbook with Sheet name "Finishes
Checklists" (see below data example), I need to run the report using Advanced
Filter, where all rows with "N" will only show on the report, but the report
must be on a new workbook then will prompt the user to save that workbook.

FINISHES Checklists Template

A B C D
(Column)
Location Item Completed Defects
Description
Y/N

Dining Ceiling N W5 - damage
Lobby Wall Y


FINISHES Report (after running Macro, on new workbook)

A B D (Column)
Location Item Defects Description

Dining Ceiling W5 - damage

Thank you.









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced filter macro Boss Excel Programming 3 December 11th 08 09:31 AM
Advanced Filter Macro Alex.W Excel Discussion (Misc queries) 1 April 4th 08 12:19 PM
Advanced Filter Macro Dolphinv4 Excel Discussion (Misc queries) 2 March 20th 08 11:42 AM
Using Advanced Filter through Macro Prashant Garg[_2_] Excel Programming 4 December 18th 04 01:28 AM
VB Application for Advanced filter Macro Poogy Excel Programming 1 August 19th 03 03:37 PM


All times are GMT +1. The time now is 12:01 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement