this macro runs, but not with a filter on the sheets?

[matthias]

Hi guys, this macro copies sheets from one workbook to another
depending on criteria

If i run the macro when the originals sheets have a autofilter that
filters all non blanks, it gives a problem. I want him to copy the
sheets as values, so without the filter...

The problem is in the last part :" Cells.PasteSpecial xlPasteValues"!!!


If Sheets("Report Option Plan").Range("a5").Value = Sheets("Report
RSU").Range("a4").Value Then
If Sheets("Report Option Plan").Range("a5").Value = Sheets("Report
ESPP").Range("a3").Value Then
If Sheets("Report Option Plan").Range("a5").Value =
Sheets("Report F Shares").Range("a3").Value Then
Sheets(Array("Overview", "Report Option Plan", "Report RSU",
"Report ESPP", "Report F Shares")).Copy
Else
Sheets(Array("Overview", "Report Option Plan", "Report RSU",
"Report ESPP")).Copy
End If
Else
If Sheets("Report Option Plan").Range("a5").Value =
Sheets("Report F Shares").Range("a3").Value Then
Sheets(Array("Overview", "Report Option Plan", "Report RSU",
"Report F Shares")).Copy
Else
Sheets(Array("Overview", "Report Option Plan", "Report
RSU")).Copy
End If
End If
Else
If Sheets("Report Option Plan").Range("a5").Value = Sheets("Report
ESPP").Range("a3").Value Then
If Sheets("Report Option Plan").Range("a5").Value = Sheets("Report
F Shares").Range("a3").Value Then
Sheets(Array("Overview", "Report Option Plan", "Report ESPP",
"Report F Shares")).Copy
Else
Sheets(Array("Overview", "Report Option Plan", "Report
ESPP")).Copy
End If
Else
Sheets(Array("Overview", "Report Option Plan", "Report F
Shares")).Copy
End If
End If

Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Worksheets(1).Buttons.Delete
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

 

[matthias]

forgot to mention, the autofilter are manually done (not via a macro)

 

[Guest]

for each sh in ActiveWorkbook.Worksheets
sh.AutofilterMode = False
Next
Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Worksheets(1).Buttons.Delete
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

 

[matthias]

thanks for the reply, but I was not clear enough, my mistake
now the macro copies without problem, but the filters aren't applied in
the new workbook (non blank cells filter)

is this possible to be arranged

regards,
mat

 

[matthias]

hi tom
is it not possible to run the autofilter for all new sheets once the
data is copied to the new sheets

something like for each sh in ... apply autofilter on a1:a11 with
criteria:="<>"??

thanks