Copy whole column from closed workbook into open workbook

Question

I am trying to copy data from a closed workbook into an open workbook, setup is as follows:

Closed workbook (random filename, random sheet name) has data in single sheet, column A.
Open workbook needs to paste data into existing "data" sheet in the next available column

It seems very simple, but I have been having a hell of a time trying to get it to work, this is the best i can do below, but it returns with an out of range error.

Sub Test

Dim fileName As Variant
Dim tableName, hideRow As String
Dim sheetRange As Range
Dim i As Integer
Dim freecolumn As Integer
Dim newWorkbook As Workbook
Dim currentbook As String

'open dialogue box to get new file to import
fileName = Application.GetOpenFilename
        

' run update in background
'Application.ScreenUpdating = False

ThisWorkbook.Activate
Sheets("Data").Select
freecolumn = ActiveSheet.UsedRange.Columns.Count + 1
    
Set newWorkbook = Workbooks.Open(fileName, ReadOnly:=True)

' tried this method, but didnt work
'Workbooks(fileName).Worksheets(1).Range("A:A").Copy _
'Worksheets("Data").Range(freecolumn)

' also tried this
'Workbooks(fileName).Sheets(1).Columns(1).Copy Destination:=Workbooks(currentbook).Sheets("Data").Columns(freecolumn)

'Sheets("Data").Range(freecolumn).Resize(fileName.Rows.Count).Value = fileName.Value

'closedBook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'closedBook.Close SaveChanges:=False

'Application.ScreenUpdating = True

End Sub

score 1 · Accepted Answer · edited Nov 13 '21 at 03:03

Is this what you are trying? I have commented the code but If you find any bugs or have any questions, feel free to leave a comment below.

CODE

Option Explicit

Sub Sample()
    Dim Ret As Variant
    Dim wbThis As Workbook, wbThat As Workbook
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim LastCol As Long
    
    '~~> Set your current workbook
    Set wbThis = ThisWorkbook
    '~~> This is the sheet where you want to copy the data to
    Set wsThis = wbThis.Sheets("Data")
    '~~> Finding last column in data sheet
    With wsThis
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastCol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column + 1
        Else
            LastCol = 1
        End If
    End With

    '~~> Make user choose the file
    Ret = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    
    '~~> If user presses cancel
    If Ret = False Then Exit Sub
    
    '~~> Open workbook
    Set wbThat = Workbooks.Open(Ret)
    '~~> Work with sheet 1
    Set wsThat = wbThat.Sheets(1)
    
    '~~> Copy and paste the columns
    wsThat.Columns(1).Copy wsThis.Columns(LastCol)
    
    '~~> Close the file without saving
    wbThat.Close (False)
End Sub

WORTH A READ

Copy whole column from closed workbook into open workbook

1 Answers1