How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
blank, and if it is not blank, paste to the next blank row in Sheet2?

I have an idea of how to copy form 1 and paste to 2, in the first blank row
in 2:
Worksheets("Sheet1").Range("2:2").EntireRow.Copy
Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow

However, I can't figure out how to test if Sheet2, Row10 is blank, and if it
is blank, paste there, but if it is not blank, paste in the first blank row
on Sheet 2.

Regards,
Ryan---

Hi
Ryan

I'm a bit confused, do you want to paste to row 10 or 20 ? I assume
that it's row 10.

Try this

Sub move()
If Range("A10").Value = "" Then
TargetRow = 10
Else
TargetRow = Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Worksheets("Sheet1").Rows(2).Copy Destination:=Worksheets
("Sheet2").Cells(TargetRow, "A")
End Sub


Regards,
Per


On 23 Nov., 06:14, RyGuy <Ry...@discussions.microsoft.com> wrote:
> How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
> blank, and if it is not blank, paste to the next blank row in Sheet2?
>
> I have an idea of how to copy form 1 and paste to 2, in the first blank row
> in 2:
> Worksheets("Sheet1").Range("2:2").EntireRow.Copy
> Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow
>
> However, I can't figure out how to test if Sheet2, Row10 is blank, and ifit
> is blank, paste there, but if it is not blank, paste in the first blank row
> on Sheet 2. *
>
> Regards,
> Ryan---

maybe this:

application.count(worksheets("sheet2").rows(10)) = 0

--


Gary

"RyGuy" <(E-Mail Removed)> wrote in message
news:3939F92C-15B5-433E-9D20-(E-Mail Removed)...
> How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
> blank, and if it is not blank, paste to the next blank row in Sheet2?
>
> I have an idea of how to copy form 1 and paste to 2, in the first blank row
> in 2:
> Worksheets("Sheet1").Range("2:2").EntireRow.Copy
> Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow
>
> However, I can't figure out how to test if Sheet2, Row10 is blank, and if it
> is blank, paste there, but if it is not blank, paste in the first blank row
> on Sheet 2.
>
> Regards,
> Ryan---

One way:

Dim rDest As Range

With Worksheets("Sheet2")
If Application.CountA(.Rows(20).Cells) = 0 Then
Set rDest = .Rows(10)
Else
Set rDest = _
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
End If
Worksheets("Sheet1").Range("2:2").EntireRow.Copy Destination:=rDest
End With

In article <3939F92C-15B5-433E-9D20-(E-Mail Removed)>,
RyGuy <(E-Mail Removed)> wrote:

> How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
> blank, and if it is not blank, paste to the next blank row in Sheet2?
>
> I have an idea of how to copy form 1 and paste to 2, in the first blank row
> in 2:
> Worksheets("Sheet1").Range("2:2").EntireRow.Copy
> Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow
>
> However, I can't figure out how to test if Sheet2, Row10 is blank, and if it
> is blank, paste there, but if it is not blank, paste in the first blank row
> on Sheet 2.

for the a

application.counta(worksheets("sheet2").rows(10)) = 0

--


Gary

"Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
news:(E-Mail Removed)...
> maybe this:
>
> application.count(worksheets("sheet2").rows(10)) = 0
>
> --
>
>
> Gary
>
> "RyGuy" <(E-Mail Removed)> wrote in message
> news:3939F92C-15B5-433E-9D20-(E-Mail Removed)...
>> How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
>> blank, and if it is not blank, paste to the next blank row in Sheet2?
>>
>> I have an idea of how to copy form 1 and paste to 2, in the first blank row
>> in 2:
>> Worksheets("Sheet1").Range("2:2").EntireRow.Copy
>> Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow
>>
>> However, I can't figure out how to test if Sheet2, Row10 is blank, and if it
>> is blank, paste there, but if it is not blank, paste in the first blank row
>> on Sheet 2.
>>
>> Regards,
>> Ryan---
>
>

In spite of my (somewhat) sloppy description, you nailed it JE McGimpsey.
Thanks so much!!

Regards,
Ryan---


--
RyGuy


"JE McGimpsey" wrote:

> One way:
>
> Dim rDest As Range
>
> With Worksheets("Sheet2")
> If Application.CountA(.Rows(20).Cells) = 0 Then
> Set rDest = .Rows(10)
> Else
> Set rDest = _
> .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
> End If
> Worksheets("Sheet1").Range("2:2").EntireRow.Copy Destination:=rDest
> End With
>
> In article <3939F92C-15B5-433E-9D20-(E-Mail Removed)>,
> RyGuy <(E-Mail Removed)> wrote:
>
> > How can I copy Row 2 from Sheet1 & paste to Row 10 Sheet2, if Sheet2 Row20 is
> > blank, and if it is not blank, paste to the next blank row in Sheet2?
> >
> > I have an idea of how to copy form 1 and paste to 2, in the first blank row
> > in 2:
> > Worksheets("Sheet1").Range("2:2").EntireRow.Copy
> > Worksheets("Sheet2").Range("A65536").End(xlUp)(2).EntireRow
> >
> > However, I can't figure out how to test if Sheet2, Row10 is blank, and if it
> > is blank, paste there, but if it is not blank, paste in the first blank row
> > on Sheet 2.
>