VBA Copy and Paste

[EvonS]

I get a this error "Runtime error 1004: application defined error or object defined error"

My code

Sub Import_Data()

    Dim src As Workbook
    Dim dest As Workbook
    'Dim rng As Range
    
    Set src = Workbooks.Open([A3])
    Set dest = Workbooks.Open("T:\Datawarehouse_FOCUSToronto\Source\Source.xlsx")
    'rng = Workbooks("Discussion Notes - BC 2022.xlsm").Worksheets("Sheet1").Cells(17, 2).Value
    
    'Copy range to clipboard
    Workbooks("Discussion Notes - BC 2022.xlsm").Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy
 
    'PasteSpecial to paste values, formulas, formats, etc.
    Workbooks("Source.xlsm").Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues

 
End Sub

Any ideas of what the problem is?

 

[EvonS]

You do realize that you're not closing these wbs after opening them? That's not the same code I offerred? I missed the .Name part of the wbs. Trial this. Dave

Sub Import_Data()
Dim src As Workbook
Dim dest As Workbook
Set src = Workbooks.Open(CStr([Sheet1!A3]))
Set dest = Workbooks.Open("T:\Datawarehouse_FOCUSToronto\Source\Source.xlsx")
'copy from source wb
With Workbooks(src.Name).Worksheets("Data Fields")
.Range(.Cells(9583, 1), .Cells(10337, 1)).Copy
'.Range("A9583:A10337").Copy
End With
'paste to destination wb
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks(dest.Name).Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
'******CLOSE BOTH WBs HERE
End Sub

I really appreciate you help with this. The problem ended up being that the dest and src were reversed in the code you initially gave me. I made that change and now it works perfectly. Thanks. Now I have to figure out how to get it not paste into the first empty cell

 

[NdNoviceHlp]

I initially stated that it was odd that U were copying from the destination file and pasting to the source file, but I left the code as U had it. You seemed to have changed this, so my previous post followed your lead. To avoid the use of the clipboard you can use the following. It places the transferred range to the next available row based on the "B" column. Dave

Sub Import_Data()
Dim src As Workbook, Rng As Range
Dim dest As Workbook, LastRow As Integer
Set src = Workbooks.Open(CStr([Sheet1!A3]))
Set dest = Workbooks.Open("T:\Datawarehouse_FOCUSToronto\Source\Source.xlsx")
With Workbooks(src.Name).Worksheets("Data Fields")
Set Rng = .Range(.Cells(9583, 1), .Cells(10337, 1))
End With
With Workbooks(dest.Name).Worksheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("B" & LastRow + 1).Resize(Rng.Rows.Count, _
                       Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With

'******CLOSE BOTH WBs HERE
End Sub

 

[EvonS]

I initially stated that it was odd that U were copying from the destination file and pasting to the source file, but I left the code as U had it. You seemed to have changed this, so my previous post followed your lead. To avoid the use of the clipboard you can use the following. It places the transferred range to the next available row based on the "B" column. Dave

Sub Import_Data()
Dim src As Workbook, Rng As Range
Dim dest As Workbook, LastRow As Integer
Set src = Workbooks.Open(CStr([Sheet1!A3]))
Set dest = Workbooks.Open("T:\Datawarehouse_FOCUSToronto\Source\Source.xlsx")
With Workbooks(src.Name).Worksheets("Data Fields")
Set Rng = .Range(.Cells(9583, 1), .Cells(10337, 1))
End With
With Workbooks(dest.Name).Worksheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("B" & LastRow + 1).Resize(Rng.Rows.Count, _
                       Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With

'******CLOSE BOTH WBs HERE
End Sub

Thank you so much. Have an awesome day.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome(s). Dave