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?

Hi Rick,

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

=vlookup(lookup_value,table_array,col_index_num,ra nge_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 shee
reference)
-col_index is the column to return (ie, sales column etc from othe
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 lik
this:
Sheets("custinfo").Range("A1:A6").Copy _
Destination:=Sheets("invoice").Range("C2"

--
UofMo
-----------------------------------------------------------------------
UofMoo's Profile: http://www.excelforum.com/member.php...fo&userid=2648
View this thread: http://www.excelforum.com/showthread.php?threadid=39760

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,ra nge_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

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).Cop y _
Destination:=Sheets("invoice").Range("C"&destinati onRow)
next

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

--
UofMo
-----------------------------------------------------------------------
UofMoo's Profile: http://www.excelforum.com/member.php...fo&userid=2648
View this thread: http://www.excelforum.com/showthread.php?threadid=39760

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).Cop y _
Destination:=Sheets("invoice").Range("C"&destinati onRow)
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