Copy named range, but skip blanks

  • Follow


My goal here is to copy "NewRecord" and paste values only in the first blank 
cell in column A on the Payment History sheet, while skipping blanks rows.

"NewRecord" has 22 rows with formulas in columns A, B & C.  Sometimes all 22 
rows will have data entered, sometimes only the 1st row. 

The marco works, but does not skip the blank rows in NewRecord - is it 
because there are formulas present?  How do I fix this? 

Also, how do I assure my macro is pasting on the first blank row of column 
A?    


Application.Goto Reference:="NewRecord"
    Selection.Copy
    Sheets("Payment History").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Range("A292").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
        :=True, Transpose:=False
End Sub


0
Reply Utf 12/14/2009 5:52:01 PM

LastRow = Sheets("Payment History").Range("A" &
Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

NumberofRows = Range("NewRecord").Rows.Count

For RowCount = 1 To NumberofRows
If Range("NewRecord").Offset(RowCount - 1, 0) _
.Resize(1, 1).Value <> "" Then

Range("NewRecord").Rows(RowCount).Copy
Sheets("Payment History").Range("A" & NewRow).PasteSpecial _
Paste:=xlPasteValues
NewRow = NewRow + 1
End If

Next RowCount


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=162385

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
Reply joel 12/14/2009 6:34:50 PM

1 Replies
844 Views

(page loaded in 0.01 seconds)


Reply: