Form a customer list from invoices

**coffcons** · 11-19-2009, 08:30 PM

I'm trying to figure out a way to get my invoice templates to copy the names and addresses from them to another worksheet to form a customer database if you will.

**davesexcel** · 11-19-2009, 08:45 PM

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

**coffcons** · 11-20-2009, 03:32 PM

Originally Posted by davesexcel

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

Sorry about the repeat post.

These are the two workbooks:

Customer Details.zip

Proposal.zip

(Hope I did this right.)

**coffcons** · 11-20-2009, 09:38 PM

Anyone please?

**khamilton** · 11-20-2009, 10:17 PM

Are you going to have every proposal on a different tab in the proposal workbook?

**davesexcel** · 11-21-2009, 03:50 PM

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

**coffcons** · 11-21-2009, 04:52 PM

Originally Posted by davesexcel

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

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

how do I debug this?

**davesexcel** · 11-21-2009, 04:54 PM

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?

**coffcons** · 11-21-2009, 04:57 PM

Originally Posted by davesexcel

Are both workbooks open?

Yes. I opened both of the ones you sent.

**coffcons** · 11-21-2009, 04:55 PM

Originally Posted by khamilton

Are you going to have every proposal on a different tab in the proposal workbook?

No. Each one will be on a separate workbook and saved separately.

**Coder** · 11-21-2009, 07:34 PM

I get subscript out of range too, I'm using xl2002. If I change the code to this it works:

Sub SendData()

    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Name")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 1) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Phone")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 2) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Address")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 3) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("City")

End Sub

edit: too slow! I see you got there already.

**coffcons** · 11-21-2009, 07:49 PM

Originally Posted by Coder

I get subscript out of range too, I'm using xl2002. If I change the code to this it works:

Sub SendData()

    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Name")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 1) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Phone")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 2) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("Address")
    Workbooks("Customer Details.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 3) = Workbooks("Proposal1.xls").Worksheets("Sheet1").Range("City")

End Sub

edit: too slow! I see you got there already.

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.

**davesexcel** · 11-21-2009, 09:45 PM

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.

**coffcons** · 11-22-2009, 01:33 PM

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?

**davesexcel** · 11-22-2009, 06:42 PM

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

Sub SendData()

    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Set wb1 = Workbooks("Proposal1")
    Set wb2 = Workbooks("Customer Details")
    Set ws1 = wb1.Worksheets("Sheet1")
    Set ws2 = wb2.Worksheets("Sheet1")

    ws2.Range("A65536").End(xlUp).Offset(1, 0) = ws1.Range("Name")
    ws2.Range("A65536").End(xlUp).Offset(0, 1) = ws1.Range("Phone")
    ws2.Range("A65536").End(xlUp).Offset(0, 2) = ws1.Range("Address")
    ws2.Range("A65536").End(xlUp).Offset(0, 3) = ws1.Range("City")

    ChDir "C:\CustomerTest"
    On Error Resume Next
    MkDir (Range("A10").Text)
    ActiveWorkbook.SaveAs Filename:="C:\CustomerTest\" & Range("A10") & "\Proposal1.xls"
    
End Sub

Form a customer list from invoices

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Re: Form a customer list from invoices

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions