0

I am new to VBA and am trying to create an automatic filing system. I want to copy invoice information directly over from an invoice 'InvoiceMaker' workbook to a separate 'InvoiceTracker' workbook each time an invoice has been filled out. I have a long line of free invoice numbers in the 'Tracker' workbook and i want to assign specific information from each new invoice to each free invoice number. I have been writing to code to automatically search for the invoice number using 'find' in the column B where these free invoice numbers are located. Once this has been found the information in specific cells can be copied over directly to this row of cells.

I have come across a problem with finding the location of the invoice numbers in the external workbook. The error message 91 keeps recurring 'Object Variable or With block variable not set', even though a name has been given an object and set correctly. The find result comes up with 'nothing' even though there is a value in that cell in this external workbook. I changed the format of the cell and it still does not work!

Sub test()
    Dim Source As Workbook
    Dim Destination As Workbook
    Dim Rng As Range
    Dim RowNumber As Long
    Dim InvoiceNumber As String
    'Names the workbooks
    Set Source = Workbooks.Open("C:\Desktop\InvoiceMaker.xlsm")
    Set Destination = Workbooks.Open("C:\Desktop\InvoiceTrack.xlsx")
    'Picks the invoice number
    InvoiceNumber = Source.Sheets(1).Range("H9")
    'Finds the row of the sheet

The problem stems from below with 'error 91' and I cannot seem to problem solve it! I cannot seem to achieve anything but 'nothing' upon running. All of the sheet locations are correct, I have tried everything!

    Set Rng = Destination.Sheets(1).Columns("B:B").Find(What:=InvoiceNumber, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    RowNumber = Rng.Row` 


     `'Copies the data from the source
    Source.Sheets(1).Range("C8").Copy
    'Pastes to the destination
    Destination.Sheets(1).Cells(RowNumber, 3).PasteSpecial
    Source.Sheets(1).Range("H11").Copy
    Destination.Sheets(1).Cells(RowNumber, 4).PasteSpecial
    Source.Sheets(1).Range("C5").Copy
    Destination.Sheets(1).Cells(RowNumber, 5).PasteSpecial
    Source.Sheets(1).Range("H10").Copy
    Destination.Sheets(1).Cells(RowNumber, 6).PasteSpecial
    Source.Save
    'Destination.Save
    'Destination.Close
End Sub

If someone is kind enough to look over this for me, can you please look at the special paste section and correct me if i am coding incorrectly!

Thanks!

George West
  • 1
  • 1
  • 1
    I would suggest stepping through the code line by line so you can get a better idea of what's going on and localize the issue. Also see this overview of [Debugging VBA](https://stackoverflow.com/questions/50189158/debugging-vba-locating-problems-and-troubleshooting-methods) from Chip Pearson. You should also see the [tour] (which earns a badge!) as well as the [help/ontopic] and also "[ask]" and plus "[mcve]". – ashleedawg Jun 29 '18 at 06:03
  • 1
    It is probably because if `Find` doesn't find anything then `Rng` is `Nothing` and that doesn't have a `.Row`. So probably check directly after `Find()` if `If Rng Is Nothing Then Exit Sub 'nothing found so exit` – Pᴇʜ Jun 29 '18 at 06:17
  • have you tried adding yourcode.PasteSpecial paste:=xlPasteValues ? Also I'd try replacing cells(RowNumber,3) by Range("C"&RowNumber) –  Jun 29 '18 at 07:48
  • I have tried debugging it and have added in different numbers for the `Find()` number and still returns 'nothing' under the locals window. Is there another way of locating cells containing specific numbers in another workbook? The `Exit Sub` doesnt seem to solve the overarching problem or am i wrong? – George West Jun 29 '18 at 17:34
  • @GeorgeWest Are you sure that you are acting on the correct sheet? `Sheets(1)` means the first sheet in the bottom tab bar, no matter what it is named. Be aware that `Sheet1`, `Sheets("Sheet1")` and `Sheets(1)` can be 3 totally different sheets. – Pᴇʜ Jul 02 '18 at 06:08

0 Answers0