Application Defined or Object Defined Error

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Code:
Dim wb1 As Workbook
Dim wb4 As Workbook


Set wb1 = Application.Workbooks("General_Account_Ledger.xls")
Set wb4 = ActiveWorkbook
        
        wb4.Sheets("Customer Funds Received").Range("B10").Copy
        Sheets("Customer Ledger").Range("A3").Select
        Selection.End(xlDown).Offset.Cells(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        wb4.Sheets("Customer Funds Received").Range("A16").Copy
        wb4.Sheets("Customer Ledger").Range("A3").Select

Here is the code I am having trouble with. I am receiving an application Defined or Object Defined Error on line

Code:
Selection.End(xlDown).Offset.Cells(1, 0).Select

I am not totally sure why this is, but if its object defined then my range object might not fit with the offset command.

Any way around this?
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You probably don't have data below A3 and it's going to the last cell in the column, which can't have an offset of 1 row, because it's the last row. Also, no need for selecting anything.

In looking at your code you're copying one cell to another, then just copying another cell and doing nothing with it. Not sure what you're trying to do here. A detailed explanation would help. I'm not entirely sure what the 'wb1' variable is for. And I'd be careful about using ActiveWorkbook. If the code is being called in the desired workbook, then 'ThisWorkbook' is a more definitive object.

A point about the logic here, this isn't really the best way to go. I'm assuming for the moment that you're wanting to paste the value of the specified cell into the next available cell in column B of the 'Customer Funds Received' sheet. Instead of going from the top-down, go from the bottom-up. Of course there are things which could make this logic fallible as well, such as having data below the desired range, or having your data in a Table (both of which can be worked around though).

Also, if you're only moving values, there's no real need to copy, you can just set one cell's Value to equal the other.

The code below is untested, and I removed the last two superfluous lines of code...

Code:
    Dim wb1 As Workbook
    Dim wb4 As Workbook
    Dim CopySheet As Worksheet
    Dim PasteSheet As Worksheet
    Dim CopyRange As Range
    Dim PasteRange As Range

    Set wb1 = Application.Workbooks("General_Account_Ledger.xls")
    Set wb4 = ActiveWorkbook
    Set CopySheet = wb4.Sheets("Customer Funds Received")
    Set PasteSheet = wb4.Sheets("Customer Ledger")

    Set CopyRange = CopySheet.Range("B10")
    Set PasteRange = PasteSheet.Cells(PasteSheet.Rows.Count, "A").End(xlUp).Offset(1, 0)

    PasteRange.Value = CopyRange.Value
 
Upvote 0
Hi Zack,

Thanks for the reply!

I was pasting to the last cell in the sheet! oops

its kind of a funky script(in development) and will only be used by myself which allows for flexibility in the logic like being sure to keep the wb4(workbook variable) open/on screen when I run the script.

Thanks for your response
 
Upvote 0
No problem.

Since there's no need to select anything, all you really need to do is ensure you qualify the workbook/worksheet/range object appropriately, then there's little need for ever using Activeworkbook. It's a bad habit regardless, unless you specifically need it (i.e. an add-in).
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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