Data copy and paste in VBA

[ThrottleWorks]

Hi,

Please review attached file and help. I need to copy data from sheet 1 and paste it in sheet 2 of the same file.

ABC, DEF, GHI, JKL & MNO or unique values.
The format of Input sheet will be as provided in sample file.

There will always one blank row between two tables.

Can anyone please help me in this.

 

[Jigar B]

Something to start with:

Sub MoveData()
Dim lastRow, i As Long
    Sheets("Sheet1").Select
    lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 2 To lastRow
        If Range("B" & i) = "" Then
          Range("E" & i).Value = Range("A" & i).Value
        Else
          Range("E" & i).Value = Range("E" & i - 1).Value
        End If
    Next i
    Range("A1:E" & lastRow).AutoFilter Field:=2, Criteria1:="<>"
    ActiveSheet.Range("E1:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet2").Range("A1").PasteSpecial
    ActiveSheet.Range("A1:D" & lastRow).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet2").Range("B1").PasteSpecial
    Application.CutCopyMode = False
    ActiveSheet.AutoFilterMode = False
    Range("E:E").Clear
End Sub

Also file with code is attached.

 

[ThrottleWorks]

Hi @Jigar B , thanks a lot for the help. Am checking this and will revert with details.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @Jigar B , it is working great. Thanks a lot.

 

[Jigar B]

Glad to know that it helped you. you are welcome.