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,
Is there anyway to tweak this code so that that this issue is negated? I appreciate any input at all - thanks,
VBA Code:
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