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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team A
[/TD]
[TD]Keeper[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]K[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 3[/TD]
[TD]K[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Player 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 5[/TD]
[TD]K[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Player 6[/TD]
[TD]K[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Player 7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]Keeper[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]K[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 3[/TD]
[TD]K[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Player 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 6[/TD]
[TD]K[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Player 7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player 9[/TD]
[TD]K[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Player 10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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