| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
pasting and one other problem
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=390019 |
|
#2
|
|||
|
|||
|
Assuming all the sheets are in the same workbook:
Sub CopyIt() Dim Villa As Range Sheets("prem_wages").Range("B2").Copy _ Destination:=Sheets("ast").Range("C12") With Sheets("prem_gates").Columns("A:A") Set Villa = .Find("Aston Villa") End With If Not Villa Is Nothing Then Villa.Offset(0, 1).Copy Destination:=Sheets("ast").Range("C4") End If End Sub Hope this helps Rowan "B_Carpet" wrote: 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=390019 |
|
#3
|
|||
|
|||
|
Just my two cents -
Your post indicates there s/b an underscore in "aston_villa". You may need to modify this line of Rowan's code. Set Villa = .Find("Aston Villa") If the data could vary, you could always use a wildcard Set Villa = .Find("Aston" & "*" & "Villa") "B_Carpet" wrote: 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=390019 |
|
#4
|
|||
|
|||
|
Thanks JMB - I missed that.
"JMB" wrote: Just my two cents - Your post indicates there s/b an underscore in "aston_villa". You may need to modify this line of Rowan's code. Set Villa = .Find("Aston Villa") If the data could vary, you could always use a wildcard Set Villa = .Find("Aston" & "*" & "Villa") "B_Carpet" wrote: 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=390019 |
|
#5
|
|||
|
|||
|
You're welcome.
"Rowan" wrote: Thanks JMB - I missed that. "JMB" wrote: Just my two cents - Your post indicates there s/b an underscore in "aston_villa". You may need to modify this line of Rowan's code. Set Villa = .Find("Aston Villa") If the data could vary, you could always use a wildcard Set Villa = .Find("Aston" & "*" & "Villa") "B_Carpet" wrote: 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=390019 |
|
#6
|
|||
|
|||
|
cheers guys i will try that tomorrow :) also just to help me understand it the wild card line Set Villa = .Find("Aston" & "*" & "Villa") i am assuming here that the * means it can be any character or word in the middle like it does in batch files? -- B_Carpet ------------------------------------------------------------------------ B_Carpet's Profile: http://www.excelforum.com/member.php...o&userid=20207 View this thread: http://www.excelforum.com/showthread...hreadid=390019 |
|
#7
|
|||
|
|||
|
ok i have got a debugger problem. with the line: With Sheets("prem_gates").Columns("A:A") it seems not to like the With i think. cause when i made it: Sheets("prem_gates").Columns("A:A") Set Villa = .Find("Aston_Villa") it then found a problem with .find so how can i get round this new problem? -- B_Carpet ------------------------------------------------------------------------ B_Carpet's Profile: http://www.excelforum.com/member.php...o&userid=20207 View this thread: http://www.excelforum.com/showthread...hreadid=390019 |
|
#8
|
|||
|
|||
|
Yes, the "*" means any characters, words, spaces, or combination of the above.
The .Find won't work without the with statement. I don't see anything wrong with that piece of the code - it works ok on my machine (but that doesn't help you : ) what error message are you getting? double check your sheet name and make sure it is "prem_gates" and doesn't have any leading/trailing spaces. "B_Carpet" wrote: ok i have got a debugger problem. with the line: With Sheets("prem_gates").Columns("A:A") it seems not to like the With i think. cause when i made it: Sheets("prem_gates").Columns("A:A") Set Villa = .Find("Aston_Villa") it then found a problem with .find so how can i get round this new problem? -- B_Carpet ------------------------------------------------------------------------ B_Carpet's Profile: http://www.excelforum.com/member.php...o&userid=20207 View this thread: http://www.excelforum.com/showthread...hreadid=390019 |
|
#9
|
|||
|
|||
|
ah ok sorry it was me being blonde and forgetting i had changed the prem_gates sheet name lol cheers -- B_Carpet ------------------------------------------------------------------------ B_Carpet's Profile: http://www.excelforum.com/member.php...o&userid=20207 View this thread: http://www.excelforum.com/showthread...hreadid=390019 |
|
#10
|
|||
|
|||
|
A common problem. One way to minimize this issue is to work with the sheets
code name instead of the tab name. With the control toolbox toolbar visible, click on the properties button of the toolbar. You'll see (Name) property. This is the sheets codename and is not as likely to be changed by the user. You'll also see the Name property, which is the tab name. They can be the same name or different names. Referencing in your VBA code would change from Sheets("prem_gates").Select To Sheet1.Select (assuming the codename is Sheet1) "B_Carpet" wrote: ah ok sorry it was me being blonde and forgetting i had changed the prem_gates sheet name lol cheers -- B_Carpet ------------------------------------------------------------------------ B_Carpet's Profile: http://www.excelforum.com/member.php...o&userid=20207 View this thread: http://www.excelforum.com/showthread...hreadid=390019 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
