Ease of Copy Pasting from one workbook to other.

Hi,

I have 2 workbooks, In 1 workbook I have around 1000 names(entities) in column A and in the second workbook I have around 200 details in column A.

What I want is to copy & paste the 200 details entities to each of the 1000 names in the 1st workbook at column B. So the end result will have around 200,000 entities count.

Example:

Example1.jpg


End Result:

Example2.jpg


To manually do this for 1000x200 will take up a lot of time. Please suggest an easier way to perform this.

Thanks for the help.

Originally Posted by :) Sixthsense :)

A simple macro looping code will do that easily

But we need a sample workbook instead of picture

Thanks for the reply,

I wish I could give the file, so I can finish this horrible task...but it's considered as a confidential file and my work does not allow me to share it

Originally Posted by :) Sixthsense :)

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Sub ManipulateData()
Dim rMyRng_1 As Range, rMyRng_2 As Range, rResult As Range, r As Range, lRws As Long
Dim rPosition As Range

Set rPosition = ActiveCell

Set rMyRng_1 = Application.InputBox("Select The First Range", "Range 1  Req.", , , , , , 8)
Set rMyRng_2 = Application.InputBox("Select The Second Range", "Range 2  Req.", , , , , , 8)

Set rResult = Application.InputBox("Select The Result Cell", "Result Cell  Req.", , , , , , 8)
Set rResult = rResult.Cells(1)

Application.ScreenUpdating = False

With rMyRng_2
    .Copy
    lRws = .Rows.Count
End With

For Each r In rMyRng_1.Cells
    With rResult
        .PasteSpecial xlPasteValues
        .Offset(, 1).Resize(rMyRng_2.Rows.Count).Value = r.Value
        Set rResult = .Offset(rMyRng_2.Rows.Count)
    End With
Next r
    
rPosition.Select
    
Application.ScreenUpdating = True
    
End Sub

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Thank you so much..you just saved my weekend! I followed your steps but was getting an error when I checked the final data would be more than 3 million lines..so did by part by part and it worked.

Once again thank you and have a great weekend.