Hi
any suggestions how to improve the macro listed below. Its an absolute pain
having to change the macro each week because the data and positions alter
after updating the data.

Any help much appreciated ----- Mully
__________________________________________________ __________

Sub Button2_Click()

Range("B3:L3").Select
Selection.Copy
Range("B24").Select
ActiveSheet.Paste
Range("B4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Range("B46").Select
ActiveSheet.Paste
Range("B5:L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B25").Select
ActiveSheet.Paste
Range("B6:L7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B47").Select
ActiveSheet.Paste
Range("B8:L8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B26").Select
ActiveSheet.Paste
Range("B9:L9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B35").Select
ActiveSheet.Paste
Range("B10:L10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B49").Select
ActiveSheet.Paste
Range("B11:L11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B27").Select
ActiveSheet.Paste
Range("B12:L12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B50").Select
ActiveSheet.Paste
Range("B13:L13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B28").Select
ActiveSheet.Paste
Range("B14:L17").Select
Application.CutCopyMode = False
Selection.Copy
Range("B29").Select
ActiveSheet.Paste
Range("B18:L18").Select
Application.CutCopyMode = False
Selection.Copy
Range("B36").Select
ActiveSheet.Paste
Range("L20").Select
Range("B19:L19").Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveSheet.Paste
Range("B22:L22").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Range("A2").Select
End Sub

Sub Button2_Click()

Range("B3:L3").Copy Range("B24")
Range("B4:L4").Copy Range("B46")
Range("B5:L5").Copy Range("B25")
Range("B6:L7").Copy Range("B47")
Range("B8:L8").Copy Range("B26")
Range("B9:L9").Copy Range("B35")
Range("B10:L10").Copy Range("B49")
Range("B11:L11").Copy Range("B27")
Range("B12:L12").Copy Range("B50")
Range("B13:L13").Copy Range("B28")
Range("B14:L17").Copy Range("B29")
Range("B18:L18").Copy Range("B36")
Range("B19:L19").Copy Range("B33")
Range("B22:L22").Copy Range("B51")
Range("A2").Select
Application.CutCopyMode = False
End Sub


--
HTH

Bob Phillips

"mully" wrote in message
...
Hi
any suggestions how to improve the macro listed below. Its an absolute
pain
having to change the macro each week because the data and positions alter
after updating the data.

Any help much appreciated ----- Mully
__________________________________________________ __________

Sub Button2_Click()

Range("B3:L3").Select
Selection.Copy
Range("B24").Select
ActiveSheet.Paste
Range("B4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Range("B46").Select
ActiveSheet.Paste
Range("B5:L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B25").Select
ActiveSheet.Paste
Range("B6:L7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B47").Select
ActiveSheet.Paste
Range("B8:L8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B26").Select
ActiveSheet.Paste
Range("B9:L9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B35").Select
ActiveSheet.Paste
Range("B10:L10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B49").Select
ActiveSheet.Paste
Range("B11:L11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B27").Select
ActiveSheet.Paste
Range("B12:L12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B50").Select
ActiveSheet.Paste
Range("B13:L13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B28").Select
ActiveSheet.Paste
Range("B14:L17").Select
Application.CutCopyMode = False
Selection.Copy
Range("B29").Select
ActiveSheet.Paste
Range("B18:L18").Select
Application.CutCopyMode = False
Selection.Copy
Range("B36").Select
ActiveSheet.Paste
Range("L20").Select
Range("B19:L19").Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveSheet.Paste
Range("B22:L22").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Range("A2").Select
End Sub

Hi Bob

So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in the
same class in fact to use an old adage he's not good enough to lace his boots
- and if he's an of International class Gawd 'elp us all.

Now back to businessas you know I've used this community an awful lot in
recent weeks and thanks to everyone they've taught me a lot - yourself
especially!!! just tried and used the revised macro worked spot on first time
great & thanks - can I ask another question regards this worksheet and its a
damn long sheet I shortened the macro to 20 row entries from 260 rows - If I
gave each row in column B a code number as follows A1 to A10 and through the
alphabet to Z1:10 (thats why we have 260 rows) through the week after
sorting the data each day they obviously end up in different rows on the
sheet. is it possible to place say A1:A10 in order in row 265- 274 then
B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
use Column B for the Identifier the other data would then be in Columns C - M.

Thanks again ------ Mully

"Bob Phillips" wrote:

Sub Button2_Click()

Range("B3:L3").Copy Range("B24")
Range("B4:L4").Copy Range("B46")
Range("B5:L5").Copy Range("B25")
Range("B6:L7").Copy Range("B47")
Range("B8:L8").Copy Range("B26")
Range("B9:L9").Copy Range("B35")
Range("B10:L10").Copy Range("B49")
Range("B11:L11").Copy Range("B27")
Range("B12:L12").Copy Range("B50")
Range("B13:L13").Copy Range("B28")
Range("B14:L17").Copy Range("B29")
Range("B18:L18").Copy Range("B36")
Range("B19:L19").Copy Range("B33")
Range("B22:L22").Copy Range("B51")
Range("A2").Select
Application.CutCopyMode = False
End Sub


--
HTH

Bob Phillips

"mully" wrote in message
...
Hi
any suggestions how to improve the macro listed below. Its an absolute
pain
having to change the macro each week because the data and positions alter
after updating the data.

Any help much appreciated ----- Mully
__________________________________________________ __________

Sub Button2_Click()

Range("B3:L3").Select
Selection.Copy
Range("B24").Select
ActiveSheet.Paste
Range("B4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Range("B46").Select
ActiveSheet.Paste
Range("B5:L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B25").Select
ActiveSheet.Paste
Range("B6:L7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B47").Select
ActiveSheet.Paste
Range("B8:L8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B26").Select
ActiveSheet.Paste
Range("B9:L9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B35").Select
ActiveSheet.Paste
Range("B10:L10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B49").Select
ActiveSheet.Paste
Range("B11:L11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B27").Select
ActiveSheet.Paste
Range("B12:L12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B50").Select
ActiveSheet.Paste
Range("B13:L13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B28").Select
ActiveSheet.Paste
Range("B14:L17").Select
Application.CutCopyMode = False
Selection.Copy
Range("B29").Select
ActiveSheet.Paste
Range("B18:L18").Select
Application.CutCopyMode = False
Selection.Copy
Range("B36").Select
ActiveSheet.Paste
Range("L20").Select
Range("B19:L19").Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveSheet.Paste
Range("B22:L22").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Range("A2").Select
End Sub

Sorry Mully, didn't notice it was you.

Comments inline.
"mully" wrote in message
...
Hi Bob

So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in
the
same class in fact to use an old adage he's not good enough to lace his
boots
- and if he's an of International class Gawd 'elp us all.

To be prefectly honest, I think we made a mistake not letting him go last
year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not been
the same since his injury, not sure if he is in decline or still moping,
either way it is time to cash him in, so I think Wenger is correct.

I think the Jenas story is a Newcastle plant to entive your boys in. I would
put money on Jenas going to Tampa Bay Disunited than the Gooners.

We do need a strong midfield man though, to support Silva (Jenas ain't him
though).

Now back to businessas you know I've used this community an awful lot in
recent weeks and thanks to everyone they've taught me a lot - yourself
especially!!! just tried and used the revised macro worked spot on first
time
great & thanks

Pleasure as always.

can I ask another question regards this worksheet and its a
damn long sheet I shortened the macro to 20 row entries from 260 rows -
If I
gave each row in column B a code number as follows A1 to A10 and through
the
alphabet to Z1:10 (thats why we have 260 rows) through the week after
sorting the data each day they obviously end up in different rows on the
sheet. is it possible to place say A1:A10 in order in row 265- 274 then
B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
use Column B for the Identifier the other data would then be in Columns
C - M.

Not sure I get the question, but here is a stab

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
j).Address(False, False)
Next i
Next j
End Sub

Hi Bob

Now I'm on my own (Wife gone spending) had a good go with the previous and
got it working ok but because I didn't explain it properly - I have set up a
test sheet with 30 rows - all using columns B to L filled with data. In
column B on each row I inserted a code number A1:A10 / B1:B10 / C1:C10and
mixed them all up so say for example A6 could by the end of the week be on
row 17. I have jigged around with your previous code and it now will place
the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 - 53 /
C1:cC10 on rows 55-64 all in the correct order how would i get it to grab the
data in Columns C:L and put it on the same row that say A6 has moved to which
at the moment is row 38. I was wasting so much time copying and pasting just
trying to keep up to date.

Thanks and all the best for the new season it looks like we are both going
to need it
Cheers Mully

"Bob Phillips" wrote:

Sorry Mully, didn't notice it was you.

Comments inline.
"mully" wrote in message
...
Hi Bob

So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in
the
same class in fact to use an old adage he's not good enough to lace his
boots
- and if he's an of International class Gawd 'elp us all.

To be prefectly honest, I think we made a mistake not letting him go last
year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not been
the same since his injury, not sure if he is in decline or still moping,
either way it is time to cash him in, so I think Wenger is correct.

I think the Jenas story is a Newcastle plant to entive your boys in. I would
put money on Jenas going to Tampa Bay Disunited than the Gooners.

We do need a strong midfield man though, to support Silva (Jenas ain't him
though).

Now back to businessas you know I've used this community an awful lot in
recent weeks and thanks to everyone they've taught me a lot - yourself
especially!!! just tried and used the revised macro worked spot on first
time
great & thanks

Pleasure as always.

can I ask another question regards this worksheet and its a
damn long sheet I shortened the macro to 20 row entries from 260 rows -
If I
gave each row in column B a code number as follows A1 to A10 and through
the
alphabet to Z1:10 (thats why we have 260 rows) through the week after
sorting the data each day they obviously end up in different rows on the
sheet. is it possible to place say A1:A10 in order in row 265- 274 then
B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
use Column B for the Identifier the other data would then be in Columns
C - M.

Not sure I get the question, but here is a stab

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
j).Address(False, False)
Next i
Next j
End Sub

Is this it?

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = _
Cells(i, j).Address(False, False)
Next i
Next j

For i = 1 To 17
j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
Next i
End Sub


--
HTH

Bob Phillips

"mully" wrote in message
...
Hi Bob

Now I'm on my own (Wife gone spending) had a good go with the previous and
got it working ok but because I didn't explain it properly - I have set up
a
test sheet with 30 rows - all using columns B to L filled with data. In
column B on each row I inserted a code number A1:A10 / B1:B10 / C1:C10and
mixed them all up so say for example A6 could by the end of the week be on
row 17. I have jigged around with your previous code and it now will place
the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 - 53 /
C1:cC10 on rows 55-64 all in the correct order how would i get it to grab
the
data in Columns C:L and put it on the same row that say A6 has moved to
which
at the moment is row 38. I was wasting so much time copying and pasting
just
trying to keep up to date.

Thanks and all the best for the new season it looks like we are both going
to need it
Cheers Mully

"Bob Phillips" wrote:

Sorry Mully, didn't notice it was you.

Comments inline.
"mully" wrote in message
...
Hi Bob

So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not
in
the
same class in fact to use an old adage he's not good enough to lace
his
boots
- and if he's an of International class Gawd 'elp us all.

To be prefectly honest, I think we made a mistake not letting him go
last
year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not
been
the same since his injury, not sure if he is in decline or still moping,
either way it is time to cash him in, so I think Wenger is correct.

I think the Jenas story is a Newcastle plant to entive your boys in. I
would
put money on Jenas going to Tampa Bay Disunited than the Gooners.

We do need a strong midfield man though, to support Silva (Jenas ain't
him
though).

Now back to businessas you know I've used this community an awful lot
in
recent weeks and thanks to everyone they've taught me a lot -
yourself
especially!!! just tried and used the revised macro worked spot on
first
time
great & thanks

Pleasure as always.

can I ask another question regards this worksheet and its a
damn long sheet I shortened the macro to 20 row entries from 260
rows -
If I
gave each row in column B a code number as follows A1 to A10 and
through
the
alphabet to Z1:10 (thats why we have 260 rows) through the week after
sorting the data each day they obviously end up in different rows on
the
sheet. is it possible to place say A1:A10 in order in row 265- 274
then
B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I
would
use Column B for the Identifier the other data would then be in
Columns
C - M.

Not sure I get the question, but here is a stab

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
j).Address(False, False)
Next i
Next j
End Sub