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, Apr 04 '19 at 16:47
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, Apr 04 '19 at 17:07

score 0 · Answer 1 · answered Apr 04 '19 at 17:20

0

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

answered Apr 04 '19 at 17:20

Zack E

696
7
23

Thanks for the reply, unfortunately code above does not add the next imported data to the next row. – Kenster Apr 05 '19 at 12:50
did you change the ranges to match where your data is coming from and where its going? – Zack E Apr 05 '19 at 12:54
When I add in the line: "lRow = targetSheet.Range("A" & lRows).Count.End(xlUp).Row" I get error - "Compile error: Invalid qualifier" and highlights "Count" When i remove the above line and leave "targetSheet.Range("A1" & lRow).Value = sourceSheet.Range("A1").Value" it add in the cells about 10 rows down from a1 but when i browse for second excel sheet it over rights the current rows. For my tests im only using column A with 5 cells of data (A1 to A5). – Kenster Apr 06 '19 at 08:43

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

1 Answers1