having a problem when saving the document

[jaunfra]

hi,
i have the next macro to copy some filtered files and i works fine

Sub copytoPSP()
    
    Dim Lastrow As Long, Nextrow As Long
    
    Application.ScreenUpdating = False
    With Sheets("Classes")
    
        Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        Nextrow = Sheets("CPLEXPSP").Range("A" & Rows.Count).End(xlUp).Row + 1
        If Nextrow < 8 Then Nextrow = 8
        
        .Range("A8:A" & Lastrow).AutoFilter , Field:=1, Criteria1:=Range("A5").Value
                
        .Range("A9:N" & Lastrow).Copy
        Sheets("CPLEXPSP").Range("A" & Nextrow).PasteSpecial xlPasteValues
       
        .AutoFilterMode = False
        
    End With
      
    Application.ScreenUpdating = True

End Sub

but when i add at the end to save automatically

...
Application.ScreenUpdating = True
ActiveWorkbook.save
End Sub

[/CODE]

the next time the macro runs it copies all the cells in the range and without filtering...
any help??
Thanks
jaun

[The_Prist]

PasteValues not work with filtering cells. You must work with each area in visible range:

Sub copytoPSP()
    Dim Lastrow As Long, Nextrow As Long
    Dim rArea As Range
    
    Application.ScreenUpdating = False
    With Sheets("Classes")
        Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        Nextrow = Sheets("CPLEXPSP").Range("A" & Rows.Count).End(xlUp).Row + 1
        If Nextrow < 8 Then Nextrow = 8
        .Range("A8:A" & Lastrow).AutoFilter , Field:=1, Criteria1:=Range("A5").Value
                
        For Each rArea In .Range("A9:N" & Lastrow).SpecialCells(12).Areas
            rArea.Value = rArea.Value
        Next rArea
        .AutoFilterMode = False
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True

End Sub

If I correctly understood the problem

[jaunfra]

hi priest, thanks for answering, unfortunately it didnt work, the macro only hide the data base and doesnt copy anything...
What i need is the following, i have a huge data base 60000 rows, with different arrival days, i need to copy only the arrivals according to a cell that have an specific day, then save the file.
It was working with the previous code, but when i added the ActiveWorkbook.Save part, the macro start copying all the data base and not the data for the spefic day...
thanks again for your time!
Jaun