Form a customer list from invoices

Can you supply a sample workbook, so somebody can help you?

Try this out, Ensure both workbooks are open, click the button in Proposal1 to send the data to Customer Details.

Originally Posted by coffcons

Thanks Dave, however I get an error message:
Run-time error '9':
Subscript out of range


how do I debug this?

Are both workbooks open?

Originally Posted by coffcons

Yes. I opened both of the ones you sent.

The error usually happens when the code cannot find the other workbook, these would mean the workbook is not opened or you have saved it in a different name.

Originally Posted by coffcons

Yes. I opened both of the ones you sent.

Here it is again, just zipped.

I did now save both workbooks you sent into the same directory without changing either name. Still the same error.


Edit: I also downloaded your zipped files as well with the same results.

Originally Posted by coffcons

I did now save both workbooks you sent into the same directory without changing either name. Still the same error.


Edit: I also downloaded your zipped files as well with the same results.

I am not sure why, what exactly are you doing? as that error should only happen if Customer Details were not open. When you press DeBug where do you end up?

Originally Posted by davesexcel

I am not sure why, what exactly are you doing? as that error should only happen if Customer Details were not open. When you press DeBug where do you end up?

I end up at the first line of the module :

Please Login or Register  to view this content.

I assure you that both workbooks are open.

Originally Posted by coffcons

I end up at the first line of the module :

Please Login or Register  to view this content.

I assure you that both workbooks are open.

Maybe you need to dim the variables try this.

Originally Posted by coffcons

I didn't see any difference in that last file that you sent, and the results were the same. I did make some small progress, however. I added the file suffix (.xls) to the workbooks in your code and I don't get that error message anymore. The problem that I now have is that either the specified range or the offset is wrong because I'm getting my company heading copied over every time instead of the customers names and addresses.

Can you show me how to address that?

I don"t know what is wrong, you should not have had to change anything.
What version of XL are you using?

Originally Posted by coffcons

2002. I did exactly what 'Coder' did below in the next post. This allows it to find the books, but like I say the cells it is sending over are the wrong ones. I'm getting my Company header. Thanks.

I kept playing with it and I think that I finally figured it out. I see now that the second "range" describes the referenced cells and once I figured out and then changed the range cells to where they should be, I was able to get the proper transfer.

Thank you for all the help.

I just thought of something that might affect this working properly. This 'Proposal' form is a template form that when filled out for each new customer, subsequently gets re-named and filed in that customer's folder. Does this mean that I'll need a new macro with new code each time I use this or does it somehow automatically get changed on its own. I suppose the alternative would be to save the form under the same name. The problem of course is that it doesn't allow for unique file names. Sorry for my naivete.

Originally Posted by coffcons

Yes, exactly! That's precisely what I did. That gets it to find the workbooks but now I'm trying to better identify the proper cells.

LOL, I had named the ranges to the cells in the Company header, if you click on the Company Header, Select the Company name and you will see the name of the cell is "Name", Click on the Phone Number and you will see the cell is named "Phone". That is what is getting transferred to the other Workbook.

If you go to Insert=>name=>define Click on one of the names and change the cell reference to the cell reference you want.

Sorry for the confusion.

Originally Posted by davesexcel

LOL, I had named the ranges to the cells in the Company header, if you click on the Company Header, Select the Company name and you will see the name of the cell is "Name", Click on the Phone Number and you will see the cell is named "Phone". That is what is getting transferred to the other Workbook.

If you go to Insert=>name=>define Click on one of the names and change the cell reference to the cell reference you want.

Sorry for the confusion.

Thanks Dave,

I figured that out and got it all to work properly. Thanks for getting me set up with that code.

I wonder if you have any ideas about my other question. Namely that I would prefer to be able to save each new 'proposal1' form under a more specific name in that particular customer's folder. Is there a function that allows for this?

Perhaps 'ThisWorkbook' or "ActiveWorkBook" functions?

I believe this should work for you. This will create a Subfolder in a folder named "CustomerTest", which will already exist.

Please Login or Register  to view this content.

Originally Posted by coffcons

I fixed the xls file extension issues that I had with the previous code, but the error I'm getting with this code is the line

Please Login or Register  to view this content.

this is what the debugger points to. I even made a workbook with that name to see if that would help and I tried adding the xls extension also without luck. Any ideas?

Try the macro recorder to see what the code does for you.
hit record macro, press F12, change the directory and select a file, stop the recorder and look at what the code gave you.
My last post said that "CustomerTest" folder had to already exist.

Try this.

Please Login or Register  to view this content.

Originally Posted by coffcons

It works! Thanks.

I removed the 'Sub Macro' heading and attached the rest to the bottom of your other code after removing the last 'CHDIR' part. This automatically (after clicking the macro button) saves the proposal in a folder with that customers name and also saves the customers name in a separate worksheet.

Thank you for your patience.

Great, glad it worked.