VBA - copy the range in some cells and appear them into other sheet

Question

I want a code that doing the following: if the last 5 characters of the text value in the cell in column E is “(UK)” then the macro copies the range consisting of 4 cells in columns B,C,D,E in the same row and pastes below the last non-empty row in the worksheet “Sheet 1” in the same columns (so all ranges B-E with “(UK)” must be transferred to the sheet “Sheet1”);

This is not a please code this for me site. This site is for fixing broken code. Please provide code that you have tried. — deathismyfriend
The following website might be able to get you started if you want to code it yourself: homeandlearn.org. Otherwise, you can hire someone to code it for you here: careers.stackoverflow.com/employer — Ralph

PASUMPON V N PASUMPON V N · Accepted Answer · 2015-10-21T19:38:39

I am just posting my code. Hope @Jonathan will learn it.

Sub CopyC()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sheet1lastrow As Long
    Dim lastrow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("sheet1")

    lastrow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
    sheet1lastrow = ws.Range("E" & Rows.Count).End(xlUp).Row



    For i = 1 To lastrow
        If Right(ActiveSheet.Cells(i, 5).Value, 5) = "(UK)" Then
            ActiveSheet.Range(Cells(i, 2), Cells(i, 5)).Copy
            ws.Cells(sheet1lastrow + 1, 2).PasteSpecial xlValues
            Application.CutCopyMode = False
            Application.CutCopyMode = True
        End If
    Next


End Sub

VBA - copy the range in some cells and appear them into other sheet

1 Answers