so I have this part of the loop doing what I want.
For i = 3 To 100
Worksheets("Shapes").Range("I5:I7").Copy
If IsEmpty(Worksheets("MAG").Cells(i - 1, 1)) Then
Worksheets("MAG").Cells(i - 1, 1).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

Next

However, I want it to stop right after it pastes and leave the for
loop. So basically it reads the page where ever the next blank row is
it pastes then stops. thanks

If I understand your question correctly, execute an

Exit For

statement right after your Past statement.

--
Rick (MVP - Excel)


wrote in message
...
so I have this part of the loop doing what I want.
For i = 3 To 100
Worksheets("Shapes").Range("I5:I7").Copy
If IsEmpty(Worksheets("MAG").Cells(i - 1, 1)) Then
Worksheets("MAG").Cells(i - 1, 1).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

Next

However, I want it to stop right after it pastes and leave the for
loop. So basically it reads the page where ever the next blank row is
it pastes then stops. thanks

I tried it and it does nothing. It actually does up to the copy and
then stops..never pastes


On Oct 15, 1:37*pm, wrote:
so I have this part of the loop doing what I want.
For i = 3 To 100
Worksheets("Shapes").Range("I5:I7").Copy
If IsEmpty(Worksheets("MAG").Cells(i - 1, 1)) Then
Worksheets("MAG").Cells(i - 1, 1).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

Next

However, I want it to stop right after it pastes and leave the for
loop. So basically it reads the page where ever the next blank row is
it pastes then stops. thanks

It just occurs to me that you probably used a one-line If..Then statement
(as I now notice there is no End If). Change your code to this...

For i = 3 To 100
Worksheets("Shapes").Range("I5:I7").Copy
If IsEmpty(Worksheets("MAG").Cells(i - 1, 1)) Then
Worksheets("MAG").Cells(i - 1, 1).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Exit For
End If
Next

where I have converted your one-line If..Then statement into an If..Then
block and then put the Exit For statement where I intended it to go.

--
Rick (MVP - Excel)


wrote in message
...
I tried it and it does nothing. It actually does up to the copy and
then stops..never pastes


On Oct 15, 1:37 pm, wrote:
so I have this part of the loop doing what I want.
For i = 3 To 100
Worksheets("Shapes").Range("I5:I7").Copy
If IsEmpty(Worksheets("MAG").Cells(i - 1, 1)) Then
Worksheets("MAG").Cells(i - 1, 1).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

Next

However, I want it to stop right after it pastes and leave the for
loop. So basically it reads the page where ever the next blank row is
it pastes then stops. thanks