VBA Copying Ranges to the bottom of another worksheet

[Yamezz]

I'm sure this is simple, but it's got me stumped. Excel doesn't like the last line of code.
InvParticulars is a range I have named, that I want to copy from one sheet to the bottom of another sheet.
Can anyone help with my syntax?

Sub InvoiceToRecords()

LastRecordsRow = Worksheets("Invoice Records").UsedRange.Rows.Count   'determines the # of rows used
NewRecordsRow = LastRecordsRow + 2   'Row for pasting latest invoice will be 2 rows below the end of the last invoice

Sheets("Invoice").Activate

Range("InvPatrticulars").Copy Worksheets("Invoice Records").Range(NewRecordsRow, 1)

End Sub

 

[kevatarvind]

something like below

Range("InvPatrticulars").Copy Sheets("Invoice Records").Range("A" & Rows.Count).End(xlUp).Offset(2, 0)

 

[Yamezz]

Brilliant kevatarvind. Thank you.

 

[MARK858]

I personally would use the syntax kevatarvind has posted but just to show you how with similar syntax to what you were using.
Basically the word Range needed to be Cells as Rows.count returns a number not a range.
NewRecordsRow can just be replaced by +2 (less typing, that's all).

Sub InvoiceToRecords()
    Dim LastRecordsRow As Long
    LastRecordsRow = Worksheets("Invoice Records").UsedRange.Rows.Count        'determines the # of rows used
    Sheets("Invoice").Range("InvPatrticulars").Copy Worksheets("Invoice Records").[COLOR=#ff0000]Cells[/COLOR](LastRecordsRow [COLOR=#ff0000]+ 2[/COLOR], 1)
End Sub

 

[kevatarvind]

Brilliant kevatarvind. Thank you.

Your Welcome

 

[Yamezz]

Thanks Mark. You guys are always so helpful, but one day I hope to actually understand the code I'm typing. My next job is to delete any blanks rows that get copied across from the InvParticulars range, and I'm currently searching previous posts for ideas. If I can't work it out I'll start another thread looking for help on that one.

 

[kevatarvind]

My next job is to delete any blanks rows that get copied across from the InvParticulars range, and I'm currently searching previous posts for ideas. If I can't work it out I'll start another thread looking for help on that one.

That's Very Nice that first you are trying to get solution yourself
and if you are not getting any solution then come back anytime
You Are Welcome

 

[Yamezz]

I think I've found a solution:

Sub DeleteBlankRows3() 'Deletes the entire row within the selection if the ENTIRE row contains no data.

Dim Rw As Range

Selection.SpecialCells(xlCellTypeBlanks).Select

    For Each Rw In Selection.Rows

        If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

            Selection.EntireRow.Delete

        End If

    Next Rw

End With

End Sub

But it looks like it requires selecting the range I just copied (selecting the destination cells that is, not the original range).
How would I select those cells?

 

[MARK858]

Try

Sub InvoiceToRecords()
    Dim LastRecordsRow As Long, LastRow2 As Long
    LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row        'determines the # of rows used
    Sheets("Invoice").Range("[COLOR=#333333]InvPatrticulars[/COLOR]").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 2, 1)
    LastRow2 = Worksheets("Invoice Records").Range("A" & Rows.Count).End(xlUp).Row
    On Error Resume Next
    Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 1), Cells(LastRow2, 1)).SpecialCells(4).EntireRow.Delete
    On Error GoTo 0
End Sub

 

[Yamezz]

That's good thanks Mark. The only hiccup is that the average InvParticulars data looks like:

QTY ITEM PRICE
QTY ITEM PRICE
QTY ITEM PRICE
QTY ITEM PRICE
<BLANK ROW>
<BLANK ROW>
<BLANK ROW>
<BLANK ROW>
"ITEMS REQUIRING ATTENTION"
<BLANK ROW>
<BLANK ROW>
<BLANK ROW>
_________ $SUB TOTAL
_________ $TAX
_________ $TOTAL

Most invoices will have no items requiring attention, so the rows below will be blank.

The code successfully copies the InvParticulars range across and removes the blank rows between the data and the "Items Requiring Attention", but doesn't remove the blank rows between this and the totals. The data are in columns A, B and C (quantity, item, price), the heading "Items Requiring Attention" is in column A and the Sub Total, Tax and Total are in column C. Could it be that the totals being in column C, not column A is what's causing the issue?