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

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

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