Just trying to copy a cell from one open workbook to another in a macro

[shoes1]

Hi,

I have two open workbooks - one is a macro-enabled workbook (daily invoice file) created from a template and has a new name each day, and the other is a regular workbook (customer file) with a fixed name. Both are always open during the day. Depending on the customer selected in the invoice, I need to generate the next sequential number from a list, which I can do with formulas. The list is stored in the customer file (Sheet2) because the invoice file is reset each day. My problem is copying the generated number from cell "K4" of the invoice file (and it could be on any of several sheets) to the next available line of Sheet2 (column A) on the customer file, so the next time a new number is needed, it gets the next one. I tried assigning a variable, but since I won't know the filename or sheetname of the invoice file, I didn't know how to do that. I can select the range ("K4") but cannot get it to copy to the last line of the customer file without getting the runtime error '1004' method of range class failed. This is the section of code I've been using (I know it's clunky, but I'm learning as I go):

Please Login or Register  to view this content.

Thank you in advance for any help that is offered. It is very much appreciated!

[jewelsharma]

Try this macro from your customer file:

Please Login or Register  to view this content.

[shoes1]

Good morning, Thank you so much for the quick response. I still get an error, I think because the code runs from the file with the source data, not the 'copy to' file. Also, I need this all to happen behind the scenes and not require user input.

The closest I get to having this work is when I assign a variable to the sequence number to avoid using copy and paste. Unfortunately, the variable changes to '0' and replaces my source cell, but it does copy the '0' to the destination list.
Any thoughts??

Thank you

[shoes1]

Got it! I changed my variable setting to 'As String' and it no longer overwrote the value with zeroes. I don't know why, but it's working. Thank you for your help!

[jewelsharma]

I need this all to happen behind the scenes and not require user input.

To ensure this, you must have all the variables already known. As I understand, we know the workbook to copy from, we also know the cell that needs to be copied (K4), but we do not know which worksheet that cell belongs to. So, unless there is a clear concise logical way of deducing the worksheet name, a user input for the same shall be necessary.

The closest I get to having this work is when I assign a variable to the sequence number to avoid using copy and paste. Unfortunately, the variable changes to '0' and replaces my source cell, but it does copy the '0' to the destination list.

I'm sorry, but not able to follow this. Are you able to attach sample workbooks and highlight how it should work.

[shoes1]

Hi, Thank you so much for your help. Your code that declared variables 'As String' gave me the idea to declare the sequence number as that. I was declaring it as an Integer, and it seemed to take the value in cell K4 and reset it to '0'. But then I move the Dim statement to the top of the subroutine, and used 'As String' and now it works. Just to clarify, the code ran inside the source workbook with cell K4. That was the unknown filename and worksheet name. The known name was the copy destination. It now goes like this:

Please Login or Register  to view this content.

Again, thank you for being so diligent and helpful. It's wonderful to know that help is only a few clicks away!

[jewelsharma]

Happy to know that you were able to work it out yourself. Good work!