pasting and Ifelse function

[B_Carpet]

ok i want my macro to:

1)copy (from "wages" sheet) and paste one value into one cell (c12 in "ast")

2)then for excel to open another sheet ("gates") and if "A1" is the name i want it to be i want excel to copy "b2" into "C4" in "ast"

so far i got

Sheets("prem_wages").Select
Range("B2").Select
Selection.Copy
Sheets("ast").Select
Range("C12").Select

which i know will copy and select what i want for the first bit, but how do i get it to then paste it in for me?

And for the second point i am not sure where to start cause the data i get will look summat like this each week:

Newcastle 42000 Blackburn
blues 42000 Man utd
Boro 13000 Sheff utd
aston_villa 1000 Arsenal

I'd imagine it was some thing like:

Sheets("prem_gates").Select
If (A1) = aston_villa then
Range("B1").Select
Selection.Copy
Sheets("ast").Select
Range("C4").Select
else
If (A2) = aston_villa then
Range(b2).Select
Selection.Copy
Sheets("ast").Select
Range("C4").Select
else
If (A3) = aston_villa then
Range("B3").Select
Selection.Copy
Sheets("ast").Select
Range("C4").Select
else
If (A4) = aston_villa then
Range("B4").Select
Selection.Copy
Sheets("ast").Select
Range("C4").Select

Obviously i am probably wrong but if some one could help with either problem it would be great

Alex

[alf bryn]

Not sure I understand your problem but I assume you got your data in Sheet
"prem_wages" starting in the "A" cloumn and also that sheet "ast" is in the
same workbook as "prem_wages".

If so this macro may be one way of solving your problem. I also assume that
there is only one "aston_villa" value in your data set.


Sub paste_value()
'
Dim MyRange As Range, Cell As Range

Sheets("prem_wages").Activate (This line can be removed if "prem_wages"
always is the active sheet)

Set MyRange = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))

For Each Cell In MyRange

If Cell.Text = "aston_villa" Then

Range("ast!C4").Value = Cell.Offset(0, 1).Value

End If

Next Cell

End Sub


"B_Carpet" <[email protected]> wrote in
message news:[email protected]...
>
> ok i want my macro to:
>
> 1)copy (from "wages" sheet) and paste one value into one cell (c12 in
> "ast")
>
> 2)then for excel to open another sheet ("gates") and if "A1" is the
> name i want it to be i want excel to copy "b2" into "C4" in "ast"
>
> so far i got
>
> Sheets("prem_wages").Select
> Range("B2").Select
> Selection.Copy
> Sheets("ast").Select
> Range("C12").Select
>
> which i know will copy and select what i want for the first bit, but
> how do i get it to then paste it in for me?
>
> And for the second point i am not sure where to start cause the data i
> get will look summat like this each week:
>
> Newcastle 42000 Blackburn
> blues 42000 Man utd
> Boro 13000 Sheff utd
> aston_villa 1000 Arsenal
>
> I'd imagine it was some thing like:
>
> Sheets("prem_gates").Select
> If (A1) = aston_villa then
> Range("B1").Select
> Selection.Copy
> Sheets("ast").Select
> Range("C4").Select
> else
> If (A2) = aston_villa then
> Range(b2).Select
> Selection.Copy
> Sheets("ast").Select
> Range("C4").Select
> else
> If (A3) = aston_villa then
> Range("B3").Select
> Selection.Copy
> Sheets("ast").Select
> Range("C4").Select
> else
> If (A4) = aston_villa then
> Range("B4").Select
> Selection.Copy
> Sheets("ast").Select
> Range("C4").Select
>
> Obviously i am probably wrong but if some one could help with either
> problem it would be great
>
> Alex
>
>
> --
> B_Carpet
> ------------------------------------------------------------------------
> B_Carpet's Profile:
> http://www.excelforum.com/member.php...o&userid=20207
> View this thread: http://www.excelforum.com/showthread...hreadid=389754
>