VBA Import and map cells to specific cells

Question

Am very new to the VBA world so i am struggling a bit here to figure this out.

So i would like to import another excel document into my worksheet. I have managed to figure that part out.

Sub Import()
Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients savedspreadsheet,*.xls")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)

'Get data EXAMPLE
ThisWorkbook.Sheets(1).Range("A2:P2").Value = wb.Sheets(1).Range("A2:P2").Value


MsgBox ("Done")


End Sub

I am having trouble in mapping the cells that i want the information in.

So for example my data i want exported is sitting in cell A2-B2-C2 when it imports i want it in the same place of A2-B2-c2

The next lot would be D2-E2-F2 and i want them to import to Cells G2-H2-I2

The last lot would be cells G2-H2-I2-J2-K2 to import to cells K2-L2-M2-N2-O2

The data that is important could be on multiple rows anything up to 20,000 rows so the data would need to populate under each other.

Sorry if this is not clear, if you need more information please let me know

Answer 1

It's easier using a Range variable, using Resize, that way you only have to specify :

• the source range : Set SrcRg = wS.Range("A2:P2")
• the first cell you want to import to : wsTB.Range("A2")

---

Code :

Sub Import()
Dim OpenFileName As String
Dim wB As Workbook
Dim wS As Worksheet
Dim wsTB As Worksheet

Set wsTB = ThisWorkbook.Sheets(1)
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients savedspreadsheet,*.xls")
If OpenFileName = "False" Then Exit Sub
Set wB = Workbooks.Open(OpenFileName)
Set wS = wB.Sheets(1)

Dim SrcRg As Range
'Get data EXAMPLE
Set SrcRg = wS.Range("A2:P2")
wsTB.Range("A2").Resize(SrcRg.Rows.Count, SrcRg.Columns.Count).Value = SrcRg.Value

Set SrcRg = wS.Range("D2:F2")
wsTB.Range("G2").Resize(SrcRg.Rows.Count, SrcRg.Columns.Count).Value = SrcRg.Value

Set SrcRg = wS.Range("G2:K2")
wsTB.Range("K2").Resize(SrcRg.Rows.Count, SrcRg.Columns.Count).Value = SrcRg.Value



MsgBox ("Done")
End Sub