Is it possible to send cell data from one worksheet to another?

[briansandpiper]

I have a list of invoices on a worksheet and raise individual invoices on separate worksheets. Is it possible to "send" key cell data from the individual worksheets into the correct place in the master list - such information as Invoice number (which is pre-populated in the master list), customer name and invoice total value etc.?

Both the Master list and the individual invoices reside on the same server although in different folders.

Thanks

Brian

 

[SkipVought]

Hi,

How is your data organized in these workbooks?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

Each workbook represents a single invoice form with the data in individual cells.

the master workbook is simply a list of invoice numbers in rows with key data in cells along each row.

Brian

 

[SkipVought]

Do you want to BUILD the data in your master list from data that is in these various invoices?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

Yes, that is what i am stiving to achieve - the master list build with each new invoice raised

Brian

 

[SkipVought]

How does an invoice get "raised?"

Is this an event initiated by the user?

BTW, your method of storing invoice data is unconventional and has inherent pitfalls when attempting to analyze or report.

A much better approch in the long run (even if it takes you some time and effort to "convert") would be to put the data from each invoice into a common table in one sheet.

The it would be a simple matter to populate an invoice form from the data in your invoice table.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

Yes, the invoice is individually created by an operator using a standard template worksheet. The key details from each are then keyed into the master invoice list - this is the operation, i am trying to automate.

We need to keep each invoice as a separate file for accounting and archiving purposes - this is why the system has developed. We looked at a Database solution but this falls over because our invoice texts need to be individually created.

We also keep invoice data within our accounting system, but this is held in a different location, hence the need to have a local reference point.

Brian

 

[SkipVought]

What does "...because our invoice texts need to be individually created." mean?

And why does that preclude a database solution?


Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

The invoices contain unique descriptions of the services we provide. Each invoice text is different and is keyed in by the operator individually. We have found that it is simpler to use Excel as a solution for this rather than Word, or Access and prefer to keep within Excel if at all possible.

The ability to keep a master log up-to-date with detsails from each new invoice worksheet would be ideal.

 

[SkipVought]

So the operator is filling the form.

At some point, (EVENT), the form is "complete". That event would run a procedure to collect the data element values and write them to the master table. I assume that you are NOT storing the line item detail in this table.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

you are correct - once the data is entered the worksheet is saved to file with a unique filename. I am hoping that the operation of saving the file could trigger a data transfer into the separate workbook that contains the master list to update it by adding the new invoice to the bottom of the list.

Brian

 

[SkipVought]

This is a horrible process! It is uncontrolled, and each workbook that gets saved will have latent code that may cause problems down the pike since it is being driven from a template that will have to contain code or be linked to a workbook that contains code.

I'd change the process to copy the invoice sheet tab and save THAT workbook.

StoreInvoiceParameters
Set wbInv = sheets("Invoice").Copy
With wbInv
  .SaveAs "x:\NewInvName.xls"
  .Close
End With
Set wbInv = Nothing

Name each cell containing invoice parameters. Name them with the SAME NAME as the column in your master list

Sub StoreInvoiceParameters()
   Set wsMaster = Workbooks("YourMasterList.xls").Sheets("YourMasterList")
   Set wsInvoice = Workbooks("YourInvoiceTemplate.xlt").Sheets("InvoiceTemplate")
   lNextRow = wsMaster.[A1].CurrentRegion.Rows.Count + 1
   For Each n In wsInvoice.Names
      wsMaster.Cells(lNextRow, Range(n.Name).Column).Value = _
        wsInvoice.Range(n.Name).Value
   Next

End Sub

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[briansandpiper]

Skip

Many thanks for your time - I will take a look at this!

Brian