How to change value on certain cells copied from another cell with VBA

Question

I have these columns in Sheet 1 that I'll copy and paste into Sheet 2. I'll use filter on the level columns and copy only the visible cells. I wonder how to change column E values in Sheet 2.

NIP	Name	Math Score	English Score	Level
1234	Ariana	67	67	Senior High 1
1235	Brian	89	89	Senior High 2
1236	Charlie	78	90	Senior High 1
1237	Harry	85	86	Senior High 3

The data above will be pasted into Sheet 2 and I wish to change the level column's value like this:

Senior High 1 and so on -> Level 1 and so on

Thank you in advance!

Answer 1

Copy Filtered Data With Replace

Docs: Range.Replace

Sub CopyFilteredData()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet1")
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    Dim dws As Worksheet: Set dws = wb.Sheets("Sheet2")
    dws.UsedRange.Clear
    
    With dws.Range("A1")
        'srg.Rows(1).Copy
        '.PasteSpecial Paste:=xlPasteColumnWidths
        srg.Copy .Cells
        With .CurrentRegion
            .Resize(.Rows.Count - 1, 1).Offset(1, 4) _
                .Replace "Senior High", "Level", xlPart, , False
            '.EntireColumn.AutoFit
        End With
    End With
    
    MsgBox "Filtered data copied.", vbInformation
    
End Sub