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

 

[MARK858]

Can you put anything in Column C of the rows with "ITEMS REQUIRING ATTENTION" in Column A?

 

[MARK858]

Don't bother with the last question, we'll just put something in then take it out again.

Sub InvoiceToRecords2()
    Dim LastRecordsRow As Long, LastRow2 As Long
    Application.ScreenUpdating = False
    LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row        'determines the # of rows used
    Sheets("Invoice").Range("InvPatrticulars").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 2, 1)
    LastRow2 = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    For Each c In Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 1), Cells(LastRow2, 1))
        If c.Value = "ITEMS REQUIRING ATTENTION" Then c.Offset(, 2).Value = "ZZZ"
    Next
    On Error Resume Next
    Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 3), Cells(LastRow2, 3)).SpecialCells(4).EntireRow.Delete
    Range("$C$1:C" & LastRow2).AutoFilter Field:=1, Criteria1:="ZZZ"
    Range("C2:C" & LastRow2).SpecialCells(12).ClearContents
    On Error GoTo 0
    Worksheets("Invoice Records").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Yamezz]

There seems to be a hang up with the ZZZ part. In the interests of full disclosure, I have added some bits into the code that pull two extra ranges into the Invoice Records sheet, but that code seems to execute fine and doesn't seem to me to affect the rest of your code. The full code now looks like:

Sub InvoiceToRecords2()
    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("InvParticulars").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 2, 1)
        LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row        'determines the # of rows used
    Sheets("Invoice").Range("ClientParticulars").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 1, 1)
        LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row        'determines the # of rows used
    Sheets("Invoice").Range("InvDetails").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 1, 1)
    LastRow2 = Worksheets("Invoice Records").Range("A" & Rows.Count).End(xlUp).Row
    For Each c In Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 1), Cells(LastRow2, 1))
        If c.Value = "Items requiring attention" Then c.Offset(, 2).Value = "ZZZ"
    Next
    On Error Resume Next
    Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 3), Cells(LastRow2, 3)).SpecialCells(4).EntireRow.Delete
    Range("$C$1:C" & LastRow2).AutoFilter Field:=1, Criteria1:="ZZZ"
    Range("C2:C" & LastRow2).SpecialCells(12).ClearContents
    On Error GoTo 0
    Worksheets("Invoice Records").AutoFilterMode = False
End Sub

Stepping through the code, all is fine until the If/Then routine. It doesn't seem to be executing the ZZZ value bit, just looping between the If/Then statement and Next. Why would that be?

 

[MARK858]

Try stepping through the code below without altering anything and report back the results (I'll look at the alterations you made once I know the results).

Sub InvoiceToRecords2() 
    Dim LastRecordsRow As Long, LastRow2 As Long
'Application.ScreenUpdating = False
    LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row        'determines the # of rows used
    Sheets("Invoice").Range("InvPatrticulars").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 2, 1)
    LastRow2 = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    For Each c In Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 1), Cells(LastRow2, 1))
        If c.Value = "*ITEMS REQUIRING ATTENTION*" Then c.Offset(, 2).Value = "ZZZ"
    Next
    On Error Resume Next
    Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 3), Cells(LastRow2, 3)).SpecialCells(4).EntireRow.Delete
    Range("$C$1:C" & LastRow2).AutoFilter Field:=1, Criteria1:="ZZZ"
    Range("C2:C" & LastRow2).SpecialCells(12).ClearContents
    On Error GoTo 0
    Worksheets("Invoice Records").AutoFilterMode = False
'Application.ScreenUpdating = True
End Sub

 

[MARK858]

I note in your code "items requiring attention" is mostly lower case, earlier it was uppercase it must be exactly the same in the code as it is in the sheet.

 

[Yamezz]

Thanks Mark. I realised that "Items requiring attention" needed to be the same, and adjusted the code for it. I also fixed my spelling error way back when I gave you my first bit of code (InvParticulars, not InvPatriculars). The Items requiring attention initially caused an error before I changed it, but the code now moves on to the loop. Changing these two things doesn't affect the loop the code gets stuck in. All the way through I've had the screen updating on to track what it's doing too. Would it help if I attached my workbook?

 

[MARK858]

It will probably be easier if you do as it will save me guessing what your data looks like, but you will need to upload the file to a file hosting site like Box.com, mark the file for sharing then post the link in thread as you can't attach files directly.

But I still expect that there is something wrong with how you have "items requiring attention" before you try attaching the workbook can you please copy and paste in the thread exactly what "items requiring attention" appears like in your sheet and I do mean copy and paste it from the spreadsheet, do not type it.

 

[Yamezz]

I really appreciate the time you're taking to help Mark.

Here's a screenshot of the invoice. The idea being the Invoice sheet will be printed out and given to the customer, and a copy will appear in the Invoice Records sheet; though as this is just an archive copy, the idea of the code is to copy everything but the company information, then remove any blank rows to save space on the Invoice Records sheet.

 

[MARK858]

I am afraid images aren't much good as we can't test on them (and sometimes they don't tell the whole truth).
Try the code below but if it doesn't get us any further than you will need to provide a link to the file. Going to be calling it a day in a few minutes so I will try and catch up with any response tomorrow night.

Sub InvoiceToRecords2()
    Dim LastRecordsRow As Long, LastRow2 As Long
    Application.ScreenUpdating = False
    LastRecordsRow = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).row        'determines the # of rows used
    Sheets("Invoice").Range("InvDetails").Copy Worksheets("Invoice Records").Cells(LastRecordsRow + 2, 1)
    LastRow2 = Worksheets("Invoice Records").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).row
    For Each c In Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 1), Cells(LastRow2, 1))
        If Trim(c.Value) = "Items requiring attention" Then c.Offset(, 2).Value = "ZZZ"
    Next
    On Error Resume Next
    Worksheets("Invoice Records").Range(Cells(LastRecordsRow + 2, 3), Cells(LastRow2, 3)).SpecialCells(4).EntireRow.Delete
    Range("$C$1:C" & LastRow2).AutoFilter Field:=1, Criteria1:="ZZZ"
    Range("C2:C" & LastRow2).SpecialCells(12).ClearContents
    On Error GoTo 0
    Worksheets("Invoice Records").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Yamezz]

That works Mark. Thanks. I think it's all done. I can't thank you enough (or my friend with the mechanic workshop whom I'm doing this for can't thank you enough).