VB to copy invoices

[Rick, United Kingdom]

Can i use VBA to copy a row of information from two work sheets to a third
sheet that will ba an invoice. 1 sheet contains the customers details the
other containsthe sales details (costs, dates etc). is this possible?

[UofMoo]

Hi Rick,

If you just have a summary line you want copied, you could use vlookup in the invoice spreadsheet to just pull the data you want from the other two spreadsheets.

=vlookup(lookup_value,table_array,col_index_num,range_lookup)
where
-lookup_value is the company ID or some common data
-table_array is the data to look up the lookup_value in (other sheet reference)
-col_index is the column to return (ie, sales column etc from other sheets)
-range_lookup should be false unless your lookup data is sorted

If you want to use VB you'd probably want to look at something like this:
Sheets("custinfo").Range("A1:A6").Copy _
Destination:=Sheets("invoice").Range("C2")

[Rick, United Kingdom]

Excellant that worked great and the vb made it easier! ok so how do i get it
to use the next line down of customer info. i tired to use a loop but to no
avail! and could i assign this to a custom made button on the tool bar?

"UofMoo" wrote:

>
> Hi Rick,
>
> If you just have a summary line you want copied, you could use vlookup
> in the invoice spreadsheet to just pull the data you want from the
> other two spreadsheets.
>
> =vlookup(lookup_value,table_array,col_index_num,range_lookup)
> where
> -lookup_value is the company ID or some common data
> -table_array is the data to look up the lookup_value in (other sheet
> reference)
> -col_index is the column to return (ie, sales column etc from other
> sheets)
> -range_lookup should be false unless your lookup data is sorted
>
> If you want to use VB you'd probably want to look at something like
> this:
> Sheets("custinfo").Range("A1:A6").Copy _
> Destination:=Sheets("invoice").Range("C2")
>
>
> --
> UofMoo
> ------------------------------------------------------------------------
> UofMoo's Profile: http://www.excelforum.com/member.php...o&userid=26485
> View this thread: http://www.excelforum.com/showthread...hreadid=397603
>
>

[UofMoo]

To copy a larger range, just modify the range:

Sheets("custinfo").Range("A1:C6").Copy _
Destination:=Sheets("invoice").Range("C2")

That should still work for you...
if you want to specify, you'd toss it in a loop like:

destinationRow = 5
for myrow=1 to 7
Sheets("custinfo").Range("A"&myrow&":C"&myrow).Copy _
Destination:=Sheets("invoice").Range("C"&destinationRow)
next

if you have this code in a Sub() then you can link the button to the sub by right clicking on the button and select Assign Macro then select the sub from the list.
Hope that works

[Rick, United Kingdom]

Hi UofMoo
Thanks you so much for your help. It works!! (not that i doubted you!)
appart from not knowing that VBA was so flexible (i thought it was just
manipulating numbers, gonna have to learn this stuff)
Can i, with vba and possibly the line you gave me below. select any customer
from the "custinfo" sheet (listed a1:a:10) and then have the row of their
details transfered to the invoice by selecting their name? (i.e highlighting
it or something)
and possibly (and i think i am strecthing the probable here) have the
chatrges details on a sheet named charges added to the invioce as well. I am
thinking that the VBA you already gave me will do this. or it can be modified
to do this. am i right?
"UofMoo" wrote:

>
> To copy a larger range, just modify the range:
>
> Sheets("custinfo").Range("A1:C6").Copy _
> Destination:=Sheets("invoice").Range("C2")
>
> That should still work for you...
> if you want to specify, you'd toss it in a loop like:
>
> destinationRow = 5
> for myrow=1 to 7
> Sheets("custinfo").Range("A"&myrow&":C"&myrow).Copy _
> Destination:=Sheets("invoice").Range("C"&destinationRow)
> next
>
> if you have this code in a Sub() then you can link the button to the
> sub by right clicking on the button and select Assign Macro then select
> the sub from the list.
> Hope that works
>
>
> --
> UofMoo
> ------------------------------------------------------------------------
> UofMoo's Profile: http://www.excelforum.com/member.php...o&userid=26485
> View this thread: http://www.excelforum.com/showthread...hreadid=397603
>
>