Run macro relative to active cell

[Hambo]

I have 17 rows of data in a sheet called "2006". I want to insert row 1 into a sheet called "Picks (1)", row 2 into "Picks (2)", etc. The data is inserted into the same range on every sheet (B6:Q6). I've created a macro to do this:

' Row06InsertPicks Macro

Sheets("Picks (1)").Range("B6:Q6").Value = Sheets("2006").Range("C46:R46").Value
Sheets("Picks (2)").Range("B6:Q6").Value = Sheets("2006").Range("C53:R53").Value
Sheets("Picks (3)").Range("B6:Q6").Value = Sheets("2006").Range("C60:R60").Value
Sheets("Picks (4)").Range("B6:Q6").Value = Sheets("2006").Range("C67:R67").Value
Sheets("Picks (5)").Range("B6:Q6").Value = Sheets("2006").Range("C74:R74").Value
Sheets("Picks (6)").Range("B6:Q6").Value = Sheets("2006").Range("C81:R81").Value
Sheets("Picks (7)").Range("B6:Q6").Value = Sheets("2006").Range("C88:R88").Value
Sheets("Picks (8)").Range("B6:Q6").Value = Sheets("2006").Range("C95:R95").Value
Sheets("Picks (9)").Range("B6:Q6").Value = Sheets("2006").Range("C102:R102").Value
Sheets("Picks (10)").Range("B6:Q6").Value = Sheets("2006").Range("C109:R109").Value
Sheets("Picks (11)").Range("B6:Q6").Value = Sheets("2006").Range("C116:R116").Value
Sheets("Picks (12)").Range("B6:Q6").Value = Sheets("2006").Range("C123:R123").Value
Sheets("Picks (13)").Range("B6:Q6").Value = Sheets("2006").Range("C130:R130").Value
Sheets("Picks (14)").Range("B6:Q6").Value = Sheets("2006").Range("C137:R137").Value
Sheets("Picks (15)").Range("B6:Q6").Value = Sheets("2006").Range("C144:R144").Value
Sheets("Picks (16)").Range("B6:Q6").Value = Sheets("2006").Range("C151:R151").Value
Sheets("Picks (17)").Range("B6:Q6").Value = Sheets("2006").Range("C158:R158").Value

End Sub

I then delete sheet "2006", import a new sheet "2006", and do the same thing with a different macro, inserting to a new row (B7:Q7) in all the "Picks" sheets. I have a total of 25 macros, one for each new row.

Is it possible to use only ONE macro that inserts the data relative to an active cell in the first "Picks" sheet?

 

[Scott Huish]

Why are you deleting a sheet, then bringing it in again?

 

[Hambo]

Hi Hotpepper,

Each new "2006" sheet is from a different player in a football contest. Each player picks a winner for every game, all 17 weeks (those are the 17 different ranges from "2006" in my first macro). So those 17 weeks have to go to 17 different "Picks" sheets, same row each sheet (e.g Player 1's picks are on row 6 in all the "Picks" sheets).

Then I delete that "2006" sheet, and start over again with the "2006" sheet from the next player, only I have to use a new macro to insert his picks on row 7 in all the "Picks" sheets.

Clear as mud?

 

[Dylan]

Try to use this code.

counter = 46
For I = 1 To 17
NextRow = Worksheets("Picks (1)").Range("B65536").End(xlUp).Row + 1
Sheets("Picks (" & I & ")").Range("B" & NextRow & ":Q" & NextRow).Value = Sheets("2006").Range("C" & counter & ":R" & counter).Value
counter = counter + 7
Next I

 

[Hambo]

I was hoping that on the sheet "Picks (1)", I might be able to select the first cell in a given player's row, run the macro, and have that player's picks inserted -- each week's picks on a separate sheet ("Picks (1)", "Picks (2)", etc.), and always on the same row corresponding to that player.

When I tried your code, it didn't do that. But I appreciate the attempt. Thanks.