Hockey Pool Updates - Need VBA Help

[dcozzy]

What I am attempting to is streamline my process of updating my hockey pool each season. It is a keeper league, and so each team can keep up to 4 players from their previous season's team. The other thing is that when a player is kept, his "draft pick value" is one round higher than his previous year's draft pick value. So, if a player is drafted in the 4th round in 2018, and the team owner would like to keep the player on his team for the 2019 season, that player's value becomes a 3rd round pick.

What I would like to do is, once I have each team's "keepers" is write a code which will:

a) transfer the information I have on the current tab to a new tab, and
b) create the new keeper value at (current value -1)

The data is set us with players in column A, a "K" in column B beside players who will be kept (i.e., only want to transfer players with a "K" beside them to the new tab, in the same cell row/column as they are currently), and the draft pick value in column C. As such:

Team A	Keeper	Value
Player 1	K	4
Player 2
Player 3	K	10
Player 4
Player 5	K	6
Player 6	K	8
Player 7
Player 8
Player 9
Player 10
Player 11
Player 12
Team B	Keeper	Value
Player 1	K	5
Player 2
Player 3	K	8
Player 4
Player 5
Player 6	K	3
Player 7
Player 8
Player 9	K	11
Player 10

<tbody>
</tbody>

I have multiple teams, each team with 12 players.

Hopefully this is enough info to get some help!

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub dcozzy()
   Sheets("[COLOR=#ff0000]RC[/COLOR]").Copy , Sheets("[COLOR=#ff0000]RC[/COLOR]")
   With ActiveSheet
      .Name = "New RC"
      .Range("B:B").SpecialCells(xlBlanks).EntireRow.ClearContents
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace("If(isnumber(@), @-1,if(@="""","""",@))", "@", .Address))
      End With
   End With
End Sub

Change sheet name in red to suit