Run time error 424 object required vba

[drop05]

Hello i am getting a run time error 424 object required

the error is happening at the part of the code where i put a ->
What my code is trying to do is copy and paste values from that range set in the code. The code before that, i have other code that has the user select the copy file and the paste file. The file path text is then put into a cell, the copy one is on d5 and paste one is d7

so instead of doing filepathopen since i have this in different modules i figured that if i had the text of the file path somewhere i can reference it and then use it in all modules and the filepathopen code is only used once where the user selects the copy and paste files
is there a way to fix/edit my code so this error doesnt happen and that it can use that file path text and just do to the sheet asked in that case "Sch C" copy the range then paste it into the other file pastes sch c range?

thank you!

code:

Sub Get_data()

Application.ScreenUpdating = False

copy_wb = ThisWorkbook.Worksheets("File Paths").Range("D5").Value
paste_wb = ThisWorkbook.Worksheets("File Paths").Range("d7").Value

-> copy_wb.Worksheets("Sch C").Range("f12:f13").Copy
paste_wb.Worksheets("Sch C").Range("E11:E12").Offset(, myvalC).PasteSpecial Paste:=xlPasteValues

 

[johnnyL]

Sorry about that delay, family affairs.

The following is what I came up with to try, I haven't tested it, but it should get you closer if not all the way to your goal:

Sub CopyFromClosedWorkbook()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath    As String
    Dim paste_wb_FullPath   As String
'
    Dim wbSource            As Workbook
    Dim wbDestination       As Workbook
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets("Sch C").Range("F12:F13").Copy                                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets("Sch C").Range("E11:E12").Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now?
End Sub

I used the option 2 approach, reopen the closed workbooks to work with them. You would have to save/and/or/close them according to your desires at the end of the subroutine.

 

[drop05]

Sorry about that delay, family affairs.

The following is what I came up with to try, I haven't tested it, but it should get you closer if not all the way to your goal:

Sub CopyFromClosedWorkbook()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath    As String
    Dim paste_wb_FullPath   As String
'
    Dim wbSource            As Workbook
    Dim wbDestination       As Workbook
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets("Sch C").Range("F12:F13").Copy                                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets("Sch C").Range("E11:E12").Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now?
End Sub

I used the option 2 approach, reopen the closed workbooks to work with them. You would have to save/and/or/close them according to your desires at the end of the subroutine.

this worked great thank you!
Is there a way that say for instance cell D5 and D7 if there was nothing in both or even if D5 had a file path but d7 did not then the code wont do the copy paste and instead a message box saying "paste/copy file path needed"?

 

[johnnyL]

Sub CopyFromClosedWorkbookV2()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath    As String
    Dim paste_wb_FullPath   As String
'
    Dim wbSource            As Workbook
    Dim wbDestination       As Workbook
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    If copy_wb_FullPath = "" Or paste_wb_FullPath = "" Then                                     ' Check to see if both paths have been supplied
        MsgBox "paste/copy file paths needed"
        Exit Sub
    End If
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets("Sch C").Range("F12:F13").Copy                                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets("Sch C").Range("E11:E12").Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now?
End Sub

 

[drop05]

Sub CopyFromClosedWorkbookV2()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath    As String
    Dim paste_wb_FullPath   As String
'
    Dim wbSource            As Workbook
    Dim wbDestination       As Workbook
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    If copy_wb_FullPath = "" Or paste_wb_FullPath = "" Then                                     ' Check to see if both paths have been supplied
        MsgBox "paste/copy file paths needed"
        Exit Sub
    End If
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets("Sch C").Range("F12:F13").Copy                                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets("Sch C").Range("E11:E12").Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now?
End Sub

awesome thank you! I actually added this to a master module runner where it basically runs the module that has the code above but in different modules just some changed ranges and sheets so like a sch a,b, and c one and added the if statement for them being blank then there and it does not run the code at all thank you!

I see you added the comment of maybe close open works now, how would i incorporate that, just curious?

 

[johnnyL]

Try this version out:

Sub CopyFromClosedWorkbookV3()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath            As String
    Dim DestinationRangeToPasteTo   As String
    Dim DestinationSheetName        As String
    Dim paste_wb_FullPath           As String
    Dim SourceRangeToCopy           As String
    Dim SourceSheetName             As String
'
    Dim wbDestination       As Workbook
    Dim wbSource            As Workbook
'
    DestinationSheetName = "Sch C"                                                              ' <--- Set this to the desired Source sheet name
    SourceSheetName = "Sch C"                                                                   ' <--- Set this to the desired Destination sheet name
    DestinationRangeToPasteTo = "E11:E12"                                                       ' <--- Set this to the desired Destination range to paste to
    SourceRangeToCopy = "F12:F13"                                                               ' <--- Set this to the desired Source range to copy
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    If copy_wb_FullPath = "" Or paste_wb_FullPath = "" Then                                     ' Check to see if both paths have been supplied
        MsgBox "paste/copy file paths needed"
        Exit Sub
    End If
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets(SourceSheetName).Range(SourceRangeToCopy).Copy                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets(DestinationSheetName).Range(DestinationRangeToPasteTo).Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now? False = Don't save changes, True = Savechanges
    wbSource.Close Savechanges:=False                                                           ' Close the Source workbook without saving any changes
    wbDestination.Close Savechanges:=True                                                       ' Close the Destination workbook, save any changes
End Sub

 

[drop05]

Try this version out:

Sub CopyFromClosedWorkbookV3()                                                                    ' Open and then copy from/to closed workbooks
'
    Dim copy_wb_FullPath            As String
    Dim DestinationRangeToPasteTo   As String
    Dim DestinationSheetName        As String
    Dim paste_wb_FullPath           As String
    Dim SourceRangeToCopy           As String
    Dim SourceSheetName             As String
'
    Dim wbDestination       As Workbook
    Dim wbSource            As Workbook
'
    DestinationSheetName = "Sch C"                                                              ' <--- Set this to the desired Source sheet name
    SourceSheetName = "Sch C"                                                                   ' <--- Set this to the desired Destination sheet name
    DestinationRangeToPasteTo = "E11:E12"                                                       ' <--- Set this to the desired Destination range to paste to
    SourceRangeToCopy = "F12:F13"                                                               ' <--- Set this to the desired Source range to copy
'
    Application.ScreenUpdating = False                                                          ' Disable screen updating to reduce screen flicker
'
    copy_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D5")                        ' copy_wb_FullPath = "C:\users\Me\Desktop\CopySource.xlsx"
    paste_wb_FullPath = ThisWorkbook.Worksheets("File Paths").Range("D7")                       ' paste_wb_FullPath = "C:\users\Me\Desktop\PasteSource.xlsm"
'
    If copy_wb_FullPath = "" Or paste_wb_FullPath = "" Then                                     ' Check to see if both paths have been supplied
        MsgBox "paste/copy file paths needed"
        Exit Sub
    End If
'
    Set wbSource = Workbooks.Open(copy_wb_FullPath)                                             ' Open the Source workbook
    Set wbDestination = Workbooks.Open(paste_wb_FullPath)                                       ' Open the Destination workbook
'
    wbSource.Sheets(SourceSheetName).Range(SourceRangeToCopy).Copy                              ' Copy range F12:F13 from the Source sheet of wbSource
    wbDestination.Sheets(DestinationSheetName).Range(DestinationRangeToPasteTo).Offset(, myvalC).PasteSpecial Paste:=xlPasteValues   ' Paste copied range to the Destination sheet of wbDestination
'
    Application.ScreenUpdating = True                                                           ' Reenable screen updating
'
'   Maybe Close open workbooks now? False = Don't save changes, True = Savechanges
    wbSource.Close Savechanges:=False                                                           ' Close the Source workbook without saving any changes
    wbDestination.Close Savechanges:=True                                                       ' Close the Destination workbook, save any changes
End Sub

This is awesome! Thank you so much

 

[johnnyL]

Your welcome @drop05.