Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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").Co py 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(lngLas tRow, 1), Cells (lngLastRow + 52, 10)).PasteSpecial xlPasteAll Worksheets(strDestWorksheetName).Range(Cells(lngLa stRow, 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(lngL astRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004, Application-definded or object-defined error
Is there behaps a typo in the line
Worksheets(strDestWorksheetame).Range(Cells(lngLas tRow, 1), Cells Should be Worksheets(strDestWorksheetName).Range(Cells(lngLa stRow, 1), Cells *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004, Application-definded or object-defined error
Is there behaps a typo in the line Worksheets(strDestWorksheetame).Range(Cells(lngLas tRow, 1), Cells Should be Worksheets(strDestWorksheetName).Range(Cells(lngLa stRow, 1), Cells No, sorry, the Typo's only in the post. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(lngLas tRow, 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(lngLas tRow, 1), Cells Should be Worksheets(strDestWorksheetName).Range(Cells(lngLa stRow, 1), Cells No, sorry, the Typo's only in the post. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(lngLas tRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(lngLas tRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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(lngLas tRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004, Application-definded or object-defined error
"Dave Peterson" wrote in message
... Worksheets(strDestWorksheetame).Range(Cells(lngLas tRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... Worksheets(strDestWorksheetame).Range(Cells(lngLas tRow, 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004, Application-definded or object-defined error
"Dave Peterson" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Time Error 1004 - Application-defined or object-defined error | Excel Programming | |||
Run-time Error 1004: Application-defined or Object-defined Error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
Runtime Error 1004 -- Application Defined or Object Defined Error | Excel Programming |