Run Time Error 1004 Application-defined or object-defined error forsimple loop? Need help with what'

[Naji]

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

 

[Naji]

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

 

[Dave Peterson]

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.