pasting and one other problem

B

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
 
R

Rowan

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
 
J

JMB

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")
 
R

Rowan

Thanks JMB - I missed that.

JMB said:
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")
Click to expand...
 
B

B_Carpet

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

B_Carpet

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?
 
J

JMB

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

B_Carpet

ah ok sorry it was me being blonde and forgetting i had changed the
prem_gates sheet name lol

cheers
 
J

JMB

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)
 
Top