Issue with macro copying a range from a random number table into another table

[chrissharples]

Hi. Fairly new to Excel and I am running W10 with MS Office 365

I have a table (random D8 dice rolls) that generates random numbers 1-8 in each cell, these are dice rolls for D8s. There are 3 rows of 7 (21 dice in total). Row 1 is red dice, row 2 blue dice and row 3 green dice, 7 dice of each colour.

These dice roll results are then transferred to another table  (combat rolls) but only filling in the cells equal to how many dice have been rolled. So if a player rolls 3 of each colour, only the first 3 cells of each coloured row will show up. The remaining cells left blank. These values match the random roll values, which is great and what I wanted.

As we know when something is done in the sheet the random roll table (random D8 dice rolls) will update and copy the new values into my other table (combat rolls). I don't want this to happen. So I have created a new table and a Macro to copy and paste Values only from the table Combat rolls to a new table. The macro will copy and paste and will not change until I press the Macro button, even if the random roll table changes. This is what I wanted. However, values do not match what is being copied over. I have attached an image of the sheet explaining what happens and what is meant to happen. 

the macro to copy and paste values is 

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim xSheet As Worksheet
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("d12:j14 ").Copy
xSheet.Range("d22:j24").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

Application.ScreenUpdating = True
End Sub

I have attached the spreadsheet too

[#2 · Accepted solution]

@chrissharples 

I'd avoid using formulas for the random values in D6:J8 - running the macro causes the formulas to update.

Instead, I'd add a button to roll the dice.

See the attached version.

[#3]

@Hans Vogelaar 

Thank you so much. It works and having a macro for the random dice is a great idea. 

For reference, I am trying to re-create a combat mechanism from a board game (World of Warcraft the board game) into excel to make it easier to carry out the combat phase. I have still lots to do but I will muddle on through it utill I hit a road block.