Hello, for some reason Excel doesn't like when I try to use the "range method" in my code below.
I would appreciate any insight as I need this to be a dynamic range.
This line doesn't work:
But if I change it to this line, it does work:
I would appreciate any insight as I need this to be a dynamic range.
This line doesn't work:
Code:
targetSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Value = sourceSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Value
But if I change it to this line, it does work:
Code:
targetSheet.Range("A1", "F9").Value = sourceSheet.Range("A1", "F9").Value
Code:
Sub TBD()
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim Source_LastColumn As Integer
Dim Source_Lastrow As Integer
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets("2 - Insert Data")
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
'STEP 2: Identify the last column and last row with data on the active worksheet
Source_LastColumn = customerWorkbook.Sheets(1).UsedRange.Columns.Count
Source_Lastrow = customerWorkbook.Sheets(1).UsedRange.Rows.Count
targetSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Value = sourceSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Value
' Close customer workbook
customerWorkbook.Close
End Sub
Last edited: