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.

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 ***

> 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.

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

> 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?

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>> > 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> >> > 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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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