Require Last two column Data copy from another sheet

Option Explicit

Sub sandeep()
    Dim lr As Long, lc As Long
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s2.Range("B" & Rows.Count).End(xlUp).Row
    lc = s2.Cells(5, Columns.Count).End(xlToLeft).Column
    s2.Range(Cells(5, lc - 1), Cells(lr, lc)).Copy
    s1.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    MsgBox "completed"
End Sub

Merged cells do not work in VBA. Unmerge the cells and change this line of code.

s2.Range(Cells(5, lc - 1), Cells(lr, lc)).Copy

to

s2.Range(Cells(3, lc - 1), Cells(lr, lc)).Copy

Read this thread

http://www.vbaexpress.com/forum/show...h-merged-cells

and this one

https://www.quora.com/Why-are-people...merge-in-excel