+ Reply to Thread
Results 1 to 10 of 10

Error 1004, Application-definded or object-defined error

  1. #1
    Mirco Wilhelm
    Guest

    Error 1004, Application-definded or object-defined error

    Hi,

    I get an error 1004 in every run but the first in this function in Excel
    2002:

    ---
    Public Function CopyWorksheetContent(strSrcWorksheetName As String,
    strDestWorksheetName As String) As Boolean
    Dim strSheetName As String
    Dim lngLastRow As Long

    Worksheets(strSrcWorksheetName).Range("A1:J52").Copy

    If Not WorksheetNameExists(strDestWorksheetName) Then
    strSheetName = AddSheetAtEnd(strDestWorksheetName)
    End If

    lngLastRow = FindLastRow(strDestWorksheetName, 2)

    If lngLastRow > 2 Then
    lngLastRow = lngLastRow + 1
    End If

    Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), Cells
    (lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    Worksheets(strDestWorksheetName).Range(Cells(lngLastRow, 1), Cells
    (lngLastRow + 52, 10)).AutoFormat Format:=xlRangeAutoFormatSimple,
    Number:=False, Font:=False, Alignment:=False, Border:=False, Pattern:=False,
    Width:=True

    Worksheets(strDestWorksheetName).Cells(lngLastRow, 1).Select

    CopyWorksheetContent = True
    End Function
    ---

    FindLastRow returns 2 in the first and 53 in the second run when it stop at
    the "Worksheets(strDestWorksheetName).Range(Cells(lngLastRow, 1), Cells
    (lngLastRow + 52, 10)).PasteSpecial xlPasteAll" line returning "Error 1004 -
    Application-defined or object-defined error"

    And here's the even weirder part: This only happenes if I start the initial
    Sub from a button on an Excel sheet. If I start the same Sub from within the
    VBA Editor I don't get this error... and in some cases I can resume the
    function by only clicking on the Play-button in the VBA Editor.



  2. #2
    Edward Ulle
    Guest

    Re: Error 1004, Application-definded or object-defined error

    Is there behaps a typo in the line

    Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), Cells

    Should be

    Worksheets(strDestWorksheetName).Range(Cells(lngLastRow, 1), Cells





    *** Sent via Developersdex http://www.developersdex.com ***

  3. #3
    Mirco Wilhelm
    Guest

    Re: Error 1004, Application-definded or object-defined error


    > Is there behaps a typo in the line
    >
    > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), Cells
    >
    > Should be
    >
    > Worksheets(strDestWorksheetName).Range(Cells(lngLastRow, 1), Cells


    No, sorry, the Typo's only in the post.



  4. #4
    Dave Peterson
    Guest

    Re: Error 1004, Application-definded or object-defined error

    Without seeing the real code, it's difficult to know for sure, but...

    This has unqualified ranges. If this code is in a general module then the
    unqualified cells will refer to the activesheet.

    Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll

    I'd qualify them using with/end with (to save typing):

    with Worksheets(strDestWorksheetame)
    .Range(.Cells(lngLastRow, 1), _
    .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    end with

    The dots in front of .range(), .cells() means that these things belong to the
    object in the previous with statement.

    You have a couple to fix.

    Mirco Wilhelm wrote:
    >
    > > Is there behaps a typo in the line
    > >
    > > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), Cells
    > >
    > > Should be
    > >
    > > Worksheets(strDestWorksheetName).Range(Cells(lngLastRow, 1), Cells

    >
    > No, sorry, the Typo's only in the post.


    --

    Dave Peterson

  5. #5
    Mirco Wilhelm
    Guest

    Re: Error 1004, Application-definded or object-defined error

    > This has unqualified ranges. If this code is in a general module then the
    > unqualified cells will refer to the activesheet.


    Jep, that's just what it's supposed to do, copy content from one sheet in
    ThisWorkbook to another

    > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    > Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    >
    > I'd qualify them using with/end with (to save typing):
    >
    > with Worksheets(strDestWorksheetame)
    > .Range(.Cells(lngLastRow, 1), _
    > .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    > end with


    Ok, I see the point, but why does this function work on it's first run but
    not on any of the following?



  6. #6
    Dave Peterson
    Guest

    Re: Error 1004, Application-definded or object-defined error

    My bet is that on the first run, strDestWorksheetame was the active worksheet.

    Then you/your code got lucky.

    Mirco Wilhelm wrote:
    >
    > > This has unqualified ranges. If this code is in a general module then the
    > > unqualified cells will refer to the activesheet.

    >
    > Jep, that's just what it's supposed to do, copy content from one sheet in
    > ThisWorkbook to another
    >
    > > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    > > Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    > >
    > > I'd qualify them using with/end with (to save typing):
    > >
    > > with Worksheets(strDestWorksheetame)
    > > .Range(.Cells(lngLastRow, 1), _
    > > .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    > > end with

    >
    > Ok, I see the point, but why does this function work on it's first run but
    > not on any of the following?


    --

    Dave Peterson

  7. #7
    Mirco Wilhelm
    Guest

    Re: Error 1004, Application-definded or object-defined error

    Not really, the button to start this function is on another Worksheet.

    This File hast 5 Worksheets. On the first are an overview and the command
    buttons.

    This function creates sheet 6 (if it doesn't exist), takes sheet 5 and
    copies it into the first free row on sheet 6. after this is finished, it
    activates the first cell in this row on sheet 6. So i'll never have an
    active sheet 5 or 6 until the script finishes, and I'll have to switch to
    sheet 1 again ro restart it.

    "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    news:43BEED76.8EBCE344@verizonXSPAM.net...
    > My bet is that on the first run, strDestWorksheetame was the active
    > worksheet.
    >
    > Then you/your code got lucky.
    >
    > Mirco Wilhelm wrote:
    >>
    >> > This has unqualified ranges. If this code is in a general module then
    >> > the
    >> > unqualified cells will refer to the activesheet.

    >>
    >> Jep, that's just what it's supposed to do, copy content from one sheet in
    >> ThisWorkbook to another
    >>
    >> > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    >> > Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    >> >
    >> > I'd qualify them using with/end with (to save typing):
    >> >
    >> > with Worksheets(strDestWorksheetame)
    >> > .Range(.Cells(lngLastRow, 1), _
    >> > .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    >> > end with

    >>
    >> Ok, I see the point, but why does this function work on it's first run
    >> but
    >> not on any of the following?

    >
    > --
    >
    > Dave Peterson




  8. #8
    Mirco Wilhelm
    Guest

    Re: Error 1004, Application-definded or object-defined error

    "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    news:43BEED76.8EBCE344@verizonXSPAM.net...
    >> > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    >> > Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    >> >
    >> > I'd qualify them using with/end with (to save typing):
    >> >
    >> > with Worksheets(strDestWorksheetame)
    >> > .Range(.Cells(lngLastRow, 1), _
    >> > .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    >> > end with


    Ok, it worked to some degree, but now it stops at this line:

    Worksheets(strDestWorksheetName).Cells(lngLastRow, 1).Select

    I tried putting it into the with and switch from select to activate,
    replacing cells with range, but it won't work unless I activate the whole
    sheet, which leaves the selection of the copied content active.



  9. #9
    Dave Peterson
    Guest

    Re: Error 1004, Application-definded or object-defined error

    You can't select a cell on a sheet that isn't active:

    Worksheets(strDestWorksheetName).select
    Worksheets(strDestWorksheetName).Cells(lngLastRow, 1).Select

    or
    application.goto Worksheets(strDestWorksheetName).Cells(lngLastRow, 1)



    Mirco Wilhelm wrote:
    >
    > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    > news:43BEED76.8EBCE344@verizonXSPAM.net...
    > >> > Worksheets(strDestWorksheetame).Range(Cells(lngLastRow, 1), _
    > >> > Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    > >> >
    > >> > I'd qualify them using with/end with (to save typing):
    > >> >
    > >> > with Worksheets(strDestWorksheetame)
    > >> > .Range(.Cells(lngLastRow, 1), _
    > >> > .Cells(lngLastRow + 52, 10)).PasteSpecial xlPasteAll
    > >> > end with

    >
    > Ok, it worked to some degree, but now it stops at this line:
    >
    > Worksheets(strDestWorksheetName).Cells(lngLastRow, 1).Select
    >
    > I tried putting it into the with and switch from select to activate,
    > replacing cells with range, but it won't work unless I activate the whole
    > sheet, which leaves the selection of the copied content active.


    --

    Dave Peterson

  10. #10
    Mirco Wilhelm
    Guest

    Re: Error 1004, Application-definded or object-defined error


    "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    news:43BFF197.1F34EAB7@verizonXSPAM.net...
    > You can't select a cell on a sheet that isn't active:
    >
    > Worksheets(strDestWorksheetName).select
    > Worksheets(strDestWorksheetName).Cells(lngLastRow, 1).Select
    >
    > or
    > application.goto Worksheets(strDestWorksheetName).Cells(lngLastRow, 1)


    thx, for the hint



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1