pasting and Ifelse function
 

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

pasting and Ifelse function
 

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" wrote in
message ...

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