Selecting Proper Range in Excel

Question

I am copying data from one workbook to another. I am continuously struggling with Range and cannot figure out as how should I insert data as I want.

            Workbook A                                 Workbook B
         ID           Name                         Id            Name
         1A                                        3C            Sarah
         2A                                        4J            Joy
         5D                                        R8            Lucas

Result

             (Expected)                                Reality
         ID           Name                         Id            Name
         1A                                        1A            Sarah
         2A                                        2A            Joy
         5D                                        5D            Lucas
         3C           Sarah                        3C
         4J           Joy                          4J
         R8           Lucas                        R8


  Sub CopyOttiCoverage()

Dim x As Workbook
Dim y As Workbook
Dim rng As Range
Dim LastRow As Long
Dim NextRow As Long

Set x = Workbooks.Open("H\A.xlsm")
Set y = ThisWorkbook 'Workbooks.Open("H:\B.xlsx")

x.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
Range("A2:A" & LastRow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0)
Range("B2:B" & LastRow).Copy y.Worksheets("Sheet1").Range("b65536").End(xlUp).Offset(1, 0)
Application.CutCopyMode = False

As shown above when I copy records from B to A, where A has all blanks in Name field, the result puts Names in wrong places. So I think that it has something to do with the range that I paste. Also if someone can tell me if its possible to insert a date column before ID column and update it with system date. Thanks in advance.

Answer 1

Change the bottom paste line to:

Range("B2:B" & LastRow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 1)

and put it above the other line. So it will look like this:

Sub CopyOttiCoverage()

Dim x As Workbook
Dim y As Workbook
Dim rng As Range
Dim LastRow As Long
Dim NextRow As Long

Set x = Workbooks.Open("H\A.xlsm")
Set y = ThisWorkbook 'Workbooks.Open("H:\B.xlsx")

x.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
Range("B2:B" & LastRow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 1)
Range("A2:A" & LastRow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0)
    Application.CutCopyMode = False

This probably isn't the best way to do this, but it should work.

EDIT: Also, you could copy the whole range at once by doing this:

Range("A2:B" & LastRow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1,0)

Then if you change the "B" to "C, D, X" whatever, you can get a whole bunch of connected columns.