Thread: Copy Column Error

Why dont you attach a sample workbook?

Hate spamming this thread, but I've been working on this stupid thing and have a working Code. Problem is, it's not very pretty. I'm pretty sure there must be a more efficient way.

Is there someone that could spot check this code and suggest a more efficient way? I don't like the fact i have to take the whole column, I'd rather just take the range of the cells that I need from the second workbook from D2 to the end of the values.

Any suggestions would be helpful.

Sub CopyFromWKB()

    Dim Wkb1 As Workbook
    Dim ws As Worksheet
    Dim OrgWork As Workbook
    Dim fileAddy As Variant
    Dim rRange As Range
    
    On Error GoTo errHandler
    
    Set OrgWork = ActiveWorkbook
    Set ws = ActiveSheet
    ' OpenDialogue to chose a file
    Set Wkb1 = Workbooks.Open(Application.GetOpenFilename(, , "Chose a File", , False))
    
    Application.ScreenUpdating = False
    
    ' Set Wkb1 = Workbooks.Open(Filename:=fileAddy)
    wkb1Name = ThisWorkbook.Name
    Call CombineTextColumns
    Wkb1.Worksheets("Sheet1").Range("D3:D200, O3:O200").Copy
    OrgWork.Activate
    Range("A2").PasteSpecial xlPasteAllExceptBorders
    Application.CutCopyMode = False
    Wkb1.Close False
    Columns("A:A").EntireColumn.AutoFit
    Application.ScreenUpdating = True
    
    
Exit Sub

errHandler:
    MsgBox "Error copying data - error " & Err.Number & _
" - " & Err.Description

End Sub

Private Sub CombineTextColumns()
' Task: Take two columns and combine the data

    Const sDELIM As String = ", "
    Dim vTxt As String
    Dim vTxt2 As String
    Dim finalText As String
    Dim i As Long
    i = 2

    Range("D2").Select
    Application.ScreenUpdating = False
    
    Do Until IsEmpty(ActiveCell)
        vTxt = Cells(i, "d").Text
        vTxt2 = Cells(i, "E").Text
        finalText = vTxt & sDELIM & vTxt2
        Cells(i, "D").Value = finalText
        ActiveCell.Offset(1, 0).Select
        i = i + 1
     Loop
    Application.ScreenUpdating = False

End Sub

You have this line of code which does not have a proper syntax -

  Wkb1.Worksheets("Sheet1").Range("D3:D200, O3:O200").Copy

You can find the last used row using this -

 lrow=Wkb1.worksheets("Sheet1").range("A" & rows.count).end(xlup).row
Wkb1.worksheets("Sheet1").range("D3:D" & lrow).copy

This probably suffices

Sub snb()
  Workbooks.Open(Application.GetOpenFilename(, , "Choose a File", , False))
    
  with thisworkbook.activesheet.cells(2,1).resize(197)
   .value=[index(D3:D200&","&E3:E200,)]
   .offset(,2)=[O3:O200]
  end with
End Sub