Hello,

Can anyone help me?

I use the command below, but still it didnt filter the rows with answer "N",
it copies all. Can I use Advanced Filter?

Please help

EXCEL INFO IS AS FOLLOWS:

A1:D4 --- MACRO BUTTON
A5:D17 --- HEADING (COMPANY NAME, ADDRESS, ETC)
A19:D20 -- FIELD NAME (Column A19 = LOCATION, Column B19 = ITEM, Column C19
= COMPLETED, C20 = Y/N, Column D19 = DEFECTS DESCRIPTION)
A21:D194 -- INFORMATION

ON THE REPORT, ONLY ROWS FROM A21:D194 WHERE COLUMN C:21:C194 = N WILL SHOW
or COPY ON THE REPORT.

ALSO, (1) IS THERE ANY WAY TO ADD IN OR INSERT PICTURES OR LOGO? (2) CHANGE
THE LETTERS, EG ON B2 "THIS IS THE CHECKLISTS" THEN CHANGE TO "THIS IS THE
REPORT" ?

DELETE rows not working. below is the command.


Sub GENREP()

Dim myC As Worksheet
Dim myS As Worksheet
Dim myR As Range
Dim wb As Workbook

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("A5:E" & myS.Cells(Rows.Count, 5).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N" ----- NOT WORKING
myS.Cells.SpecialCells(xlCellTypeVisible).Copy
myC.Range("A1").PasteSpecial xlPasteValues
myC.Range("A1").PasteSpecial xlPasteFormats
myC.Range("A1").PasteSpecial xlPasteColumnWidths
myS.ShowAllData

Intersect(myC.Range("5:" & Rows.Count), myC.Columns(3)).Delete
Shift:=xlToLeft
myC.Range("A1:C4").Delete Shift:=xlShiftToLeft ------- NOT WORKING
myC.Move

Columns("A:A").ColumnWidth = 32
Columns("B:B").ColumnWidth = 26
Columns("C:C").ColumnWidth = 98

With myC.PageSetup ------- NOT WORKING
..LeftMargin = Application.InchesToPoints(0.1)
..RightMargin = Application.InchesToPoints(0.1)
..TopMargin = Application.InchesToPoints(0.1)
..BottomMargin = Application.InchesToPoints(0.1)
..HeaderMargin = Application.InchesToPoints(0.1)
..FooterMargin = Application.InchesToPoints(0.1)
End With

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report.xls")
End Sub

I have this macro command and its working fine, o

I think there is a problem in the columns you are using

Set myR = myS.Range("A5:E" & myS.Cells(Rows.Count, 5).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N" ----- NOT WORKING

myR is 5 columns wide from A to E. You are filtering on the 3rd column in
this range which is column C. the autofilter is going into the 5th row and
not filtering rows 1 to 4. The code also requires that the data in column E
is the same number of rows as the data in column C since you are using column
E to determine the last row of data.

"exploringmacro" wrote:

Hello,

Can anyone help me?

I use the command below, but still it didnt filter the rows with answer "N",
it copies all. Can I use Advanced Filter?

Please help

EXCEL INFO IS AS FOLLOWS:

A1:D4 --- MACRO BUTTON
A5:D17 --- HEADING (COMPANY NAME, ADDRESS, ETC)
A19:D20 -- FIELD NAME (Column A19 = LOCATION, Column B19 = ITEM, Column C19
= COMPLETED, C20 = Y/N, Column D19 = DEFECTS DESCRIPTION)
A21:D194 -- INFORMATION

ON THE REPORT, ONLY ROWS FROM A21:D194 WHERE COLUMN C:21:C194 = N WILL SHOW
or COPY ON THE REPORT.

ALSO, (1) IS THERE ANY WAY TO ADD IN OR INSERT PICTURES OR LOGO? (2) CHANGE
THE LETTERS, EG ON B2 "THIS IS THE CHECKLISTS" THEN CHANGE TO "THIS IS THE
REPORT" ?

DELETE rows not working. below is the command.


Sub GENREP()

Dim myC As Worksheet
Dim myS As Worksheet
Dim myR As Range
Dim wb As Workbook

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("A5:E" & myS.Cells(Rows.Count, 5).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N" ----- NOT WORKING
myS.Cells.SpecialCells(xlCellTypeVisible).Copy
myC.Range("A1").PasteSpecial xlPasteValues
myC.Range("A1").PasteSpecial xlPasteFormats
myC.Range("A1").PasteSpecial xlPasteColumnWidths
myS.ShowAllData

Intersect(myC.Range("5:" & Rows.Count), myC.Columns(3)).Delete
Shift:=xlToLeft
myC.Range("A1:C4").Delete Shift:=xlShiftToLeft ------- NOT WORKING
myC.Move

Columns("A:A").ColumnWidth = 32
Columns("B:B").ColumnWidth = 26
Columns("C:C").ColumnWidth = 98

With myC.PageSetup ------- NOT WORKING
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.1)
End With

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report.xls")
End Sub

I have this macro command and its working fine, o

I didn't know which column you were trying to filter. That is why I
explained in detail what the code was really doing. if you explain what you
are trying to do I will fix the line.

"exploringmacro" wrote:

Hi Joel, thanks for your comments.

Can you please give advise on what will be the correct command?

thanks

"Joel" wrote:

I think there is a problem in the columns you are using

Set myR = myS.Range("A5:E" & myS.Cells(Rows.Count, 5).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N" ----- NOT WORKING

myR is 5 columns wide from A to E. You are filtering on the 3rd column in
this range which is column C. the autofilter is going into the 5th row and
not filtering rows 1 to 4. The code also requires that the data in column E
is the same number of rows as the data in column C since you are using column
E to determine the last row of data.

"exploringmacro" wrote:

Hello,

Can anyone help me?

I use the command below, but still it didnt filter the rows with answer "N",
it copies all. Can I use Advanced Filter?

Please help

EXCEL INFO IS AS FOLLOWS:

A1:D4 --- MACRO BUTTON
A5:D17 --- HEADING (COMPANY NAME, ADDRESS, ETC)
A19:D20 -- FIELD NAME (Column A19 = LOCATION, Column B19 = ITEM, Column C19
= COMPLETED, C20 = Y/N, Column D19 = DEFECTS DESCRIPTION)
A21:D194 -- INFORMATION

ON THE REPORT, ONLY ROWS FROM A21:D194 WHERE COLUMN C:21:C194 = N WILL SHOW
or COPY ON THE REPORT.

ALSO, (1) IS THERE ANY WAY TO ADD IN OR INSERT PICTURES OR LOGO? (2) CHANGE
THE LETTERS, EG ON B2 "THIS IS THE CHECKLISTS" THEN CHANGE TO "THIS IS THE
REPORT" ?

DELETE rows not working. below is the command.


Sub GENREP()

Dim myC As Worksheet
Dim myS As Worksheet
Dim myR As Range
Dim wb As Workbook

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("A5:E" & myS.Cells(Rows.Count, 5).End(xlUp))
myR.AutoFilter Field:=3, Criteria1:="N" ----- NOT WORKING
myS.Cells.SpecialCells(xlCellTypeVisible).Copy
myC.Range("A1").PasteSpecial xlPasteValues
myC.Range("A1").PasteSpecial xlPasteFormats
myC.Range("A1").PasteSpecial xlPasteColumnWidths
myS.ShowAllData

Intersect(myC.Range("5:" & Rows.Count), myC.Columns(3)).Delete
Shift:=xlToLeft
myC.Range("A1:C4").Delete Shift:=xlShiftToLeft ------- NOT WORKING
myC.Move

Columns("A:A").ColumnWidth = 32
Columns("B:B").ColumnWidth = 26
Columns("C:C").ColumnWidth = 98

With myC.PageSetup ------- NOT WORKING
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.1)
End With

ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("Finishes Report.xls")
End Sub

I have this macro command and its working fine, o