Run time error 424 object required vba

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
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
 
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:

VBA Code:
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.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:

VBA Code:
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"?
 
Upvote 0
VBA Code:
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
 
Upvote 0
VBA Code:
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?
 
Upvote 0
Try this version out:

VBA Code:
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
 
Upvote 0
Try this version out:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top