Run-time error '1004': Method 'Range' of object_worksheet failed

[jwb1012]

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:

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:

targetSheet.Range("A1", "F9").Value = sourceSheet.Range("A1", "F9").Value

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

 

[Joe4]

Are you simply trying to copy one range to another sheet?
If so, it doesn't like it when trying to post a multi-cell range, if you pick a multi-cell range as the destination. Just pick the upper left-most corner of your destination range, i.e.

targetSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Copy Destination:=sourceSheet.Cells(1, 1)

 

[jwb1012]

Hi Joe - thanks a bunch! It worked with one minor change(reversing sourceSheet and targetSheet).

sourceSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Copy Destination:=targetSheet.Cells(1, 1)

One more question... is it possible to change my code that asks user to select the file to import from, to import based on a file path located at:

targetWorkbook....Sheet("1 - Workbook Details")....Cell($C$22)

Here is my current code:

' 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)

 

[Joe4]

See here: VBA Excel prompt user to select a file in a default folder - Stack Overflow

Note: You should be able to use a variable after "ChDir" instead of using a hard-coded value.

 

[jwb1012]

Thanks Joe! Here is the updated code that works!

Sub Extract_Report_Data()
' 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
Dim Target_LastColumn As Integer
Dim Target_Lastrow As Integer
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
customerFilename = targetWorkbook.Sheets("1 - Workbook Details").Range("$C$22")
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet1 As Worksheet
Set targetSheet1 = targetWorkbook.Worksheets("2 - Report")
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
sourceSheet.Range(Cells(1, 1), Cells(Source_Lastrow, Source_LastColumn)).Copy Destination:=targetSheet1.Cells(1, 1)
' Close customer workbook
customerWorkbook.Close
            
End Sub