Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004 Application-defined or object-defined error forsimple loop? Need help with what's wrong?
I am coming across an run time error 1004 error when executing this
macro. The purpose of the macro is to find the last row of data in a range from one spreadsheet and copy it to the same row in another spreadsheet. It seems to have a problem with my paste function into the new spreadsheet. What gives? Private Sub CommandButton1_Click() For row = 2 To 32 Do Until found = True If IsEmpty(Range("C" & (row))) Then found = True Range("C" & row - 1 & ":O" & row - 1).Copy Windows("Morning-Rpt.xls").Activate Sheets("QFin").Activate Range("C" & row - 1 & ":O" & row - 1).Paste row = row + 1 Else row = row + 1 End If Loop Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004 Application-defined or object-defined errorfor simple loop? Need help with what's wrong?
On Oct 16, 9:02*am, Naji wrote:
I am coming across an run time error 1004 error when executing this macro. The purpose of the macro is to find the last row of data in a range from one spreadsheet and copy it to the same row in another spreadsheet. It seems to have a problem with my paste function into the new spreadsheet. What gives? Private Sub CommandButton1_Click() * * For row = 2 To 32 * * Do Until found = True * * If IsEmpty(Range("C" & (row))) Then * * found = True * * Range("C" & row - 1 & ":O" & row - 1).Copy * * Windows("Morning-Rpt.xls").Activate * * Sheets("QFin").Activate * * *Range("C" & row - 1 & ":O" & row - 1).Paste * * row = row + 1 * * Else * * row = row + 1 * * End If * * Loop * * Next End Sub Nevermind. the problem was fixed with the following: Sheets("qfin").Activate For row = 2 To 32 Do Until found = True If IsEmpty(Range("C" & (row))) Then found = True ' Range("C" & row).Select Range("C" & row - 1 & ":O" & row - 1).Copy Windows("Morning-Rpt.xls").Activate Sheets("QFin").Activate Sheets("Qfin").Range("C" & row - 1 & ":O" & row - 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=8# Application.CutCopyMode = False row = row + 1 Else row = row + 1 End If Loop Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004 Application-defined or object-defined error forsimple loop? Need help with what's wrong?
I'm guessing that your code is behind a worksheet--and you're using a
commandbutton placed on that worksheet. Then your unqualified ranges belong to the sheet with the code--not the activesheet. Windows("Morning-Rpt.xls").Activate Sheets("QFin").Activate Range("C" & row - 1 & ":O" & row - 1).Paste Without the selecting... 'let excel figure out how big the receiving range will be: workbooks("morning-rpt.xls").worksheets("Qfin").range("C" & row - 1) _ .pastespecial paste:=xlpasteall or me.Range("C" & row - 1 & ":O" & row - 1).Copy _ destination:=workbooks("morning-rpt.xls") _ .worksheets("Qfin").range("C" & row - 1) The Me keyword represents the object owning the code--in this case, the sheet with the commandbutton. Naji wrote: I am coming across an run time error 1004 error when executing this macro. The purpose of the macro is to find the last row of data in a range from one spreadsheet and copy it to the same row in another spreadsheet. It seems to have a problem with my paste function into the new spreadsheet. What gives? Private Sub CommandButton1_Click() For row = 2 To 32 Do Until found = True If IsEmpty(Range("C" & (row))) Then found = True Range("C" & row - 1 & ":O" & row - 1).Copy Windows("Morning-Rpt.xls").Activate Sheets("QFin").Activate Range("C" & row - 1 & ":O" & row - 1).Paste row = row + 1 Else row = row + 1 End If Loop Next End Sub -- Dave Peterson |
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 | |||
Export a chart in a GIF file. 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 |