Locating cells in an external workbook and copying over information automatically


Locating cells in an external workbook and copying over information automatically



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:DesktopInvoiceMaker.xlsm")
Set Destination = Workbooks.Open("C:DesktopInvoiceTrack.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!





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 from Chip Pearson. You should also see the tour (which earns a badge!) as well as the [help/ontopic] and also "How to Ask" and plus "Minimal, Complete, and Verifiable example".
– ashleedawg
Jun 29 at 6:03






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 at 6:17



Find


Rng


Nothing


.Row


Find()


If Rng Is Nothing Then Exit Sub 'nothing found so exit





have you tried adding yourcode.PasteSpecial paste:=xlPasteValues ? Also I'd try replacing cells(RowNumber,3) by Range("C"&RowNumber)
– IRENE G
Jun 29 at 7: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 at 17:34



Find()


Exit Sub





@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ᴇʜ
yesterday


Sheets(1)


Sheet1


Sheets("Sheet1")


Sheets(1)









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Export result set on Dbeaver to CSV

Export result set on Dbeaver to CSV Normally I use Dbeaver for windows and always export my result set like this: run my query --> select the result --> export the result set --> select export to clipboard --> done This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it. The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below: exporting data set But once I go further in the process, in the last step I get: no query Note that in "source" it was suppose to show the query that originated the result set, but instead it says just "select. As a result it does't select my result or anything (besides being "successful"). Normally my query would show up there automatically and I couldn't find any option that corrects this problem in the menus. Please help me! Thanks. Aft...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more