Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop Paste Link function from pasting an object | Excel Discussion (Misc queries) | |||
Paste function is greyed out in the toolbar and I can't use ctl V - worksheet won't allow pasting | Excel Discussion (Misc queries) | |||
IFELSE question, as it applies to multiple cells | Excel Programming | |||
Sum function when pasting | Excel Worksheet Functions | |||
Pasting a worksheet function in VBA. | Excel Programming |