sort/copy/paste issue

[rickblunt]

Hello, I am using the following code to sort data out on one sheet and then copy and paste the sorted data to a second sheet, the data that is being copied and pasted is in columns A:H. This works fine except that I am getting a message stating that there is already data there and do I want to replace it. On my second sheet I do happen to have a couple of cells over at Columns J2:K3 that contains some critical data used for the reporting, but everything else on the sheet is clear. The issue is that when I say yes then all of this data on J:K gets deleted as the new data is copied to the sheet. I do not understand why copying/pasting A:H overwrites J:K....

Is there anyway to tweak this code so that that this issue is negated? I appreciate any input at all - thanks,

Sub EmergencyShowerReport()

    Sheets("Device List").Activate
    
        Columns("A:A").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$A$400").AutoFilter Field:=1, Criteria1:= _
        "Emergency Shower"
        
        
        Cells.Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        ActiveSheet.Range("K1").Select
                        
    Sheets("Emergency Shower Report").Select
        ActiveSheet.Range("$A$1:$A$400").Select
        ActiveSheet.PasteSpecial xlPasteValuesAndNumberFormats
        
        Cells.Select
        Cells.EntireColumn.AutoFit
        
        Application.CutCopyMode = False
        
        ActiveSheet.Range("M1").Select
        
End Sub

 

[StephenCrump]

Here's your problem - by starting with Cells, you're copying entire rows:

Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Try:

Sub EmergencyShowerReport()
    
    Dim rng As Range
    
    With Sheets("Device List")
        Set rng = .Range("A1:H" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With rng
        .AutoFilter Field:=1, Criteria1:="Emergency Shower"
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Emergency Shower Report").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Emergency Shower Report").Cells.EntireColumn.AutoFit
    End With
                
End Sub

 

[rickblunt]

Thanks StephenCrump, that worked flawlessly, and it is so much cleaner and easier to understand.

So even though the cell is "blank", its still copying the blank cell (i.e. entire row) and pasting the entire blank row on to the second sheet. I had tried confining my copy range to A:H, but not in the way that you did (I did A1:H400 which did not work). I think I understand how your code is working - thank you for the knowledge and I appreciate the assistance.

 

[StephenCrump]

Thanks for posting back. I'm glad it worked for you.