VBA copy a column from an excel's file to another in the first empty column

Question

Private Sub CommandButton1_Click()
Dim selection As Variant
selection = UserForm1.ComboBox1.Text
Sheets("Sheet1").Select
Cells(1, 2) = selection
Sheets("Sheet1").Select
selection = Cells(1, 2)
namefile = "C:\Users\xxx\" & Left(selection, 1) & "\" & selection & ".xls"
Workbooks.Open Filename:=namefile



Dim wk1 As Workbook
Dim wk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet



Set wk1 = Workbooks("file1.xlsm")
Set wk2 = Workbooks(selection & ".xls")

Set sh1 = wk1.Worksheets("Sheet2")
Set sh2 = wk2.Worksheets("Sheet1")

sh2.Activate
Dim firstempty As Variant
Dim x As Integer
Dim y As Integer
Dim A1 As Variant
Dim R1 As Variant
Dim C1 As Variant
x = 0
y = 0
While x = 0
If Range(Cells(1, y), Cells(1, y)) <> "" Then
y = y + 1
Else: Range(Cells(1, y), Cells(1, y)).Select
A1 = Target.Address
R1 = Target.Row
C1 = Replace(A1, R1, "")
firstempty = (C1 & ":" & C1)
x = 1
End If
Wend

With sh1

    .Columns("D:D").Copy Destination:=sh2.Range(firstempty)
End With


End
End Sub

I need to copy column D of Sheet2 file1.xls on the first blank column of sheet1 of a second file whose name is selected by a combobox. I am having trouble defining the letter of the empty column of the second file. I am getting runtime error 424 and my debugger brings me to the point in the code:   A1 = Target.Address

What am I doing wrong?

Answer 1

Here is a much faster way to get the last column:

Private Sub CommandButton1_Click()
Dim selection As Variant
selection = UserForm1.ComboBox1.Text
Sheets("Sheet1").Select
Cells(1, 2) = selection
Sheets("Sheet1").Select
selection = Cells(1, 2)
namefile = "C:\Users\xxx\" & Left(selection, 1) & "\" & selection & ".xls"
Workbooks.Open Filename:=namefile



Dim wk1 As Workbook
Dim wk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet



Set wk1 = Workbooks("file1.xlsm")
Set wk2 = Workbooks(selection & ".xls")

Set sh1 = wk1.Worksheets("Sheet2")
Set sh2 = wk2.Worksheets("Sheet1")

Dim LastColumn As Long

LastColumn = sh2.Cells(1, Columns.Count).End(xlToLeft).Column + 1

sh1.Columns("D:D").Copy sh2.Cells(, LastColumn)

End Sub

As a side note could you explain this part of code:

Sheets("Sheet1").Select
Cells(1, 2) = selection
Sheets("Sheet1").Select
selection = Cells(1, 2)

It looks like you are getting a value then assigning the value to a cell then the cells value (that you just assigned already) back to the variable that assigned the original value.

at the most you should only need one line:

Sheets("Sheet1").Cells(1, 2) = selection

dno't get the need for the rest.

Answer 2

Shouldn't y=0 be y=1 ? Now you're referring to column 0.

And then this should work:

If Cells(1, y) <> "" Then
y = y + 1
Else
firstempty = y
x = 1
End If

And then:

.Columns("D:D").Copy Destination:=sh2.Columns(firstempty)