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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,405
Messages
5,595,947
Members
414,034
Latest member
torreyanderson

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