Copying Cell to another Worksheet

[Audax]

Please, somebody help me: pray2:

I have a workbook with 3 worksheet (Test1, Test2 and Test3).

Analyzing the worksheet ?Test1?,
If cell B1= ?Test2? I want to copy A1 of this worksheet in Cell D1 of worksheet Test2.

If cell B1= ?Test3? I want to copy A1 of this worksheet in Cell D1 of worksheet Test3, instead of worksheet Test2

Please Help me

[Bob Phillips]

You need to add formulae to D1 in Test2 and Test3 of

=IF(Test1!B1="Test2",Test1!A1,"")

=IF(Test1!B1="Test3",Test1!A1,"")

[Audax]

Thank you El XID for your prompt answer, I tried to simplify my real problem and the solution you game me work 100%, but the original problem is:

I have a workbook with 100 worksheets (Test1 thru Test100), the worksheet Test3 is a form which in the cell D1 I'll have a name of one of the worksheets, for example Test50, I want to copy several cells of the worksheet Test3 (the form) to different cells of the worksheet Test50. If cell D1 of worksheet Test3 =Test75 then I want to copy the same several cells of the worksheet Test3 (the form) to different cells of the worksheet Test75, etc.

Maybe using VBA. Can you help me again?

Thank you a lot for your patience

[Bob Phillips]

On Error Resume Next
Worksheets(Worksheets("Test1").Range("B1").Value).Range("D1").Value = _
Worksheets("Test1").Range("A1").Value
On Error Goto 0

[mdmackillop]

Sub CopyCells()
Dim Arr, a
Dim Sh As Worksheet
Set Sh = Sheets("Test3")
Arr = Array("D3", "D8", "F5")
For Each a In Arr
Sheets(Sh.Range("B1").Value).Range(a) = Sh.Range(a)
Next
End Sub

[Audax]

Thank you again. The solution give for mdmackillop work 100%, copy the cells D3, D8 and F5 of worksheet Test3 in the worksheet selected for L13, so far Excellent that I want, but the destination it will be in 3 other different cells, for ex. A5, B8 and C9 of the worksheet selected for L13. Any idea how to modify a little bit the macro?

Thank you a lot

[mdmackillop]

Sub CopyCells()
Dim Source, Tgt1, i as Long
Dim Sh As Worksheet
Set Sh = Sheets("Test3")
Source = Array("D3", "D8", "F5")
Tgt1 = Array("A5", "B8", "C9")
For i = 0 To Ubound(Source)
Sheets(Sh.Range("B1").Value).Range(Tgt1(i)) = Sh.Range(Source(i))
Next
End Sub


Edit: For i = 0 to 2 line amended

[Audax]

Thank Mdmackillop, but in the for..next loop I receive the following error message

Run-Time error 9
Subscript out of range

Any idea?

[mdmackillop]

You will get that error if:
The loop limits are incorrect so I've modified my code to cover for this.
Tgt1 has less elements than Source
The Source or Target sheets are wrongly spelled. (ie they don't exist)
.