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

Apologies for the sloppy code, I have copied and pasted so many things from this site trying to make it work and am now bamboozled.

CodePudding user response：

Is this what you are trying? (UNTESTED). 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 LastColumn 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(LastColumn)
    
    '~~> Close the file without saving
    wbThat.Close (False)
End Sub

WORTH A READ

1. Avoid the use of .Select
2. Finding last row/column in a worksheet