Run macro relative to active cell

Hambo

New Member
Joined
Feb 5, 2007
Messages
12
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why are you deleting a sheet, then bringing it in again?
 

Hambo

New Member
Joined
Feb 5, 2007
Messages
12
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

Board Regular
Joined
Jul 27, 2007
Messages
150
Try to use this code.
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

New Member
Joined
Feb 5, 2007
Messages
12
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,668
Messages
5,838,680
Members
430,563
Latest member
Raeyven

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top