VBA Macro Issue

[kenderweasel]

Hi,

I'm trying to crate a macro that will import multiple columns of data from a sheet on one workbook into the first empty row on a specific sheet on another. I've written the following code, however, this is currently only copying over the first cell (A2, as the donor sheet has columns). The data that needs to be imported is from columns A-E, and needs to be pasted into A-E on the recieving sheet. Note - once I have this working, I will need to repeat the code for another 8 sheets, which also have data that needs to be imported.

Sub Rectangle1_Click()

Dim OpenFileName As String
Dim wb As Workbook
'Select and Open Workbook
OpenFileName = Application.GetOpenFilename("VoiceID_Reports_,*.xl??")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)
'Get data
ThisWorkbook.Sheets("Train").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = wb.Sheets("TrainSuccess").Range("$A2:$E1000").Value


MsgBox ("Done")
wb.Close
End Sub

 

[Norie]

Try this.

wb.Sheets("TrainSuccess").Range("$A2:$E1000").Copy
ThisWorkbook.Sheets("Train").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[mse330]

Here is an alternative option if you don't want to go with copy/paste approach. The reason your code is only copying the first cell is because you are telling excel to put range A2:E1000 into one cell so you have to "Resize" the range to cater the required range as shown below

ThisWorkbook.Sheets("Train").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1000 - 1, 5).Value = wb.Sheets("TrainSuccess").Range("$A2:$E1000").Value

 

[kenderweasel]

Thank you so much Norie - that solution works perfectly And thanks mse330 for letting me know where my error was - learning VBA is challenging, so I did expect that I was making a very stupid mistake... :D

 

[mse330]

Glad to help … I am still learning VBA & we learn from our mistakes/challenges