copy, find next blank row, paste special location
 

Here is what I want to do, have a macro to
Sheet 1 copy b17:017
I have this done so far as
Worksheets("Sheet 1").Range("B17:O17").Copy
That works

Now how do I find the next blank row on Sheet 2
The range is between rows A12:A36
But I want to paste the values copied to the blank row to cells F??:S??
where ?? would be the found blank row number.
And if now blank rows between A12:A36 report an error and stop

copy, find next blank row, paste special location
 

If there's nothing under rows 12:36 and you can pick out a column that always
has data in it, you can use that to get the next blank row.

I'm gonna use column A:

Sub testme1()

Dim NextRow As Long

With Worksheets("sheet 2")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

If NextRow < 12 Then
NextRow = 12
ElseIf NextRow 36 Then
MsgBox "no Blank rows!"
Exit Sub
End If

Worksheets("sheet 1").Range("b17:o17").Copy _
Destination:=Worksheets("sheet 2").Cells(NextRow, "F")

End Sub

If your data isn't laid out to allow you to do something like that, you can loop
through those rows.


Option Explicit
Sub testme2()

Dim NextRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("sheet 2")
FirstRow = 12
LastRow = 36
NextRow = -1
For iRow = FirstRow To LastRow
If Application.CountA(.Rows(iRow)) = 0 Then
NextRow = iRow
Exit For
End If
Next iRow

If NextRow = -1 Then
MsgBox "no more rows"
End If
End With

Worksheets("sheet 1").Range("b17:o17").Copy _
Destination:=Worksheets("sheet 2").Cells(NextRow, "F")

End Sub




Pete wrote:

Here is what I want to do, have a macro to
Sheet 1 copy b17:017
I have this done so far as
Worksheets("Sheet 1").Range("B17:O17").Copy
That works

Now how do I find the next blank row on Sheet 2
The range is between rows A12:A36
But I want to paste the values copied to the blank row to cells F??:S??
where ?? would be the found blank row number.
And if now blank rows between A12:A36 report an error and stop

--

Dave Peterson

copy, find next blank row, paste special location
 

Try this

Sub FindBlankRow()
Dim R As Integer, C As Integer
R = 12
C = 1
With Workbooks(1)
With Worksheets(1)
.Range("B17:O17").Copy
Do While .Cells(R, C) < ""
.Cells(R, C).Activate
R = R + 1
If Cells(R, C) = "" Then
C = 6
.Cells(R, C).PasteSpecial Paste:=xlValues
Exit Do
End If
If R = 36 Then
MsgBox ("No blank rows")
Exit Do
End If
Loop
End With
End With
End Sub

Let me know if it works for you.

--
Bob Calvanese
"Pete" wrote in message
...
Here is what I want to do, have a macro to
Sheet 1 copy b17:017
I have this done so far as
Worksheets("Sheet 1").Range("B17:O17").Copy
That works

Now how do I find the next blank row on Sheet 2
The range is between rows A12:A36
But I want to paste the values copied to the blank row to cells F??:S??
where ?? would be the found blank row number.
And if now blank rows between A12:A36 report an error and stop

copy, find next blank row, paste special location
 

You can take this line of code out...

..Cells(R, C).Activate

I only put it in to test.

--
Bob Calvanese
"Bob Calvanese" wrote in message
...
Try this

Sub FindBlankRow()
Dim R As Integer, C As Integer
R = 12
C = 1
With Workbooks(1)
With Worksheets(1)
.Range("B17:O17").Copy
Do While .Cells(R, C) < ""
.Cells(R, C).Activate
R = R + 1
If Cells(R, C) = "" Then
C = 6
.Cells(R, C).PasteSpecial Paste:=xlValues
Exit Do
End If
If R = 36 Then
MsgBox ("No blank rows")
Exit Do
End If
Loop
End With
End With
End Sub

Let me know if it works for you.

--
Bob Calvanese
"Pete" wrote in message
...
Here is what I want to do, have a macro to
Sheet 1 copy b17:017
I have this done so far as
Worksheets("Sheet 1").Range("B17:O17").Copy
That works

Now how do I find the next blank row on Sheet 2
The range is between rows A12:A36
But I want to paste the values copied to the blank row to cells F??:S??
where ?? would be the found blank row number.
And if now blank rows between A12:A36 report an error and stop