Importing Excel spreadsheet data into another Excel spreadsheet - then move to next line

Question

I am importing cells from a source spreadsheet to target spreadsheet.

Using the below code I browse for the source spreadsheet from the target and import the data.

Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

My issue is importing the data to the next blank row on the target spreadsheet.

I have used this to add new row "ActiveCell.EntireRow.Insert Shift:=xlDown" when working on a single spreadsheet, but when I used it here it adds a line on the source spreadsheet (the one import data from).

I've tried this one too but can't get it to browse https://www.excelcampus.com/vba/copy-paste-another-workbook/

I want to browse for excel spreadsheet and import it then when I repeat the process for different spreadsheet it will add to the next row.

Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

Set targetWorkbook = Application.ActiveWorkbook filter = "Text files(.xlsx),.xlsx" 

caption = "Please Select an input file " 

customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim targetSheet As Worksheet: Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet: Set sourceSheet = customerWorkbook.Worksheets(1) 

targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").ValuecustomerWorkbook.Close

Where does this code reside? In a Standard Module or in Worksheet object? — Zack E
First thing you will want to do is identify the last row without data in the targetSheet by using Dim lRow As Long and then lRow = targetSheet.Range("A" & Rows.Count.End(xlUp).row (update the "A" to whatever column the data is being moved to) — Zack E

Zack E Zack E · Accepted Answer · 2019-04-04T17:20:04

Try this: (You may need to adjust the ranges based on where you want the data to be passed to)

Private Sub CommandButton1_Click()
     Dim customerBook As Workbook, targetWorkbook As Workbook
     Dim filter As String, caption As String, customerFilename As String
     Dim lRow as Long

     Set targetWorkbook = Application.ActiveWorkbook filter = "Text files(.xlsx),.xlsx" 

     caption = "Please Select an input file " 

     customerFilename = Application.GetOpenFilename(filter, , caption)

     Set customerWorkbook = Application.Workbooks.Open(customerFilename)

     Dim targetSheet As Worksheet: Set targetSheet = targetWorkbook.Worksheets(1)
     Dim sourceSheet As Worksheet: Set sourceSheet = customerWorkbook.Worksheets(1) 

targetSheet.Range("A" & lRow).Value = sourceSheet.Range("A1", "C10").Value 
customerWorkbook.Close True

End Sub

Importing Excel spreadsheet data into another Excel spreadsheet - then move to next line

1 Answers