On sheet X where you need the data;
if the data is in Sheet "Y" Cell "D9" and "E9"! Then;
The trick is to reference the sheet name with a ! next to the Cell address or Range, like SheetY! or 'SheetY'! both work. The concatenation is done by space then "&" space with no quotes around the ampersand. You must add any formatting on your own!
If D9=123 and E9=Test then,
=D9 & E9 gives 123Test and
=D9 & "and" & E9 gives 123andTest
=D9 & " and " & E9 gives 123 and Test.
Try an "If test."
If A1=1 and B1=2 and C1=3 Then,
=If(AND(A1=B1,B1=C1),A1 & " " & B1 & " " & C1, "")
==> 1 2 3
or
=IF(AND(A1=B1,B1=C1),Sum(A1:C1),"")
==> 6
The Sheet reference is: SheetY!A1 in each reference above. JSW
Set MyData= Worksheets("Sheet y").Range("D9") & Worksheets("Sheet y").Range("E9")
MyData.Copy Destination:=Range(Worksheets("Sheet X").Range("A1"").Address)
You can avoid the Sheet tag by using a with statement.
With Worksheets("Sheet y")
your code...
End With
JSW
This spreadsheet is for accounting purposes for a whole month and a client might come three times during the month. What i want is a way to getall the transactions with the same policy number, reference number, date effective and premium to be combined as one transaction and placed in another worksheet with an overall total.
Policy NO Ref. No. Date Effec premium
e.g P10000201 PA1111 07/07/2001 $25.00
P10000201 PA1111 07/07/2001 $20.00
P10000201 PA1111 07/07/2001 $10.00
How would i get this to another worksheet with a total of $55.00?
Roger Set MyData= Worksheets("Sheet y").Range("D9") & Worksheets("Sheet y").Range("E9")