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?

 

[NdNoviceHlp]

A couple of things. It's odd that your trying to copy from the destination wb to the source wb? You have not identified/opened your "Source.xlsm" wb unless that's what's in [a3}? Speaking of which, it should be [Sheet1!A3] for example if U want to use that notation. The Sheet1!A3 cell must also have the entire file path of the file needed. It more than likely needs to be forced into a string as a string is needed to open the wb ie. CSTR([Sheet1!A3). So, if U insist on copying from your destination file to your source file...

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 range to clipboard
    Dest.Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy
 
    'PasteSpecial to paste values, formulas, formats, etc.
    src.Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues

HTH. Dave

 

[EvonS]

A couple of things. It's odd that your trying to copy from the destination wb to the source wb? You have not identified/opened your "Source.xlsm" wb unless that's what's in [a3}? Speaking of which, it should be [Sheet1!A3] for example if U want to use that notation. The Sheet1!A3 cell must also have the entire file path of the file needed. It more than likely needs to be forced into a string as a string is needed to open the wb ie. CSTR([Sheet1!A3). So, if U insist on copying from your destination file to your source file...

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 range to clipboard
    Dest.Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy
 
    'PasteSpecial to paste values, formulas, formats, etc.
    src.Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues

HTH. Dave

I appreciate you taking the time to respond and making the edits. the "Source.xlsx" file was named by someone else so I can't change it. But that is where i want the data to be pasted to. A3 does contain the full path to the file.

I'm getting a syntax error for this line "Set src = Workbooks.Open(CSTR[Sheet1!A3]))" I tried deleting the extra bracket but it's still giving me a syntax error.

 

[EvonS]

A couple of things. It's odd that your trying to copy from the destination wb to the source wb? You have not identified/opened your "Source.xlsm" wb unless that's what's in [a3}? Speaking of which, it should be [Sheet1!A3] for example if U want to use that notation. The Sheet1!A3 cell must also have the entire file path of the file needed. It more than likely needs to be forced into a string as a string is needed to open the wb ie. CSTR([Sheet1!A3). So, if U insist on copying from your destination file to your source file...

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 range to clipboard
    Dest.Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy
 
    'PasteSpecial to paste values, formulas, formats, etc.
    src.Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues

HTH. Dave

Figured out the syntax error. Now I get a 'run time error 9: subscript out of range' error for this line dest.Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy

 

[NdNoviceHlp]

Whoops...

Set src = Workbooks.Open(CSTR([Sheet1!A3]))

Dave
edit: Never noticed that error. What range do you want copied?
Your syntax should be something like this but changed to actual range...

With Dest.Worksheets("Data Fields")
.Range(.Cells(17, 2),.Cells(17,2)).Copy
End With

 

[EvonS]

Whoops...

Set src = Workbooks.Open(CSTR([Sheet1!A3]))

Dave

Thanks. Figured out the syntax error. Now I get a 'run time error 9: subscript out of range' error for this line dest.Worksheets("Data Fields").Range(Cells(17, 2).Value).Copy

 

[EvonS]

Whoops...

Set src = Workbooks.Open(CSTR([Sheet1!A3]))

Dave
edit: Never noticed that error. What range do you want copied?
Your syntax should be something like this but changed to actual range...

With Dest.Worksheets("Data Fields")
.Range(.Cells(17, 2),.Cells(17,2)).Copy
End With

Still getting that same subscript error for this line "With src.Worksheets("Data Fields")". I'm trying to copy a range of cells from one workbook to another.

 

[NdNoviceHlp]

Have you now changed the copy wb to the source wb? U still need to change the range to suit. It should be...

With Workbooks(src.Name).Worksheets("Data Fields")
.Range(.Cells(17, 2),.Cells(17,2)).Copy
End With

Dave

 

[EvonS]

Have you now changed the copy wb to the source wb? U still need to change the range to suit. It should be...

With Workbooks(src.Name).Worksheets("Data Fields")
.Range(.Cells(17, 2),.Cells(17,2)).Copy
End With

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 range to clipboard
    With Workbooks("K:\FOCUS Meetings\Situation Table Tools\2022\Discussion Notes - BC 2022.xlsm").Worksheets("Data Fields")
        '.Range(.Cells(17, 2), .Cells(17, 2)).Copy
        .Range("A9583:A10337").Copy
    End With
 
    'PasteSpecial to paste values, formulas, formats, etc.
    src.Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues

End Sub

I get the same error even when I use the full path and range directly in the code. I have no idea what's going on.

 

[NdNoviceHlp]

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