Copy/Paste Range with Unknown End Point

Try something like:

Dim C As Range

Set C = Range("B:CC").SpecialCells(xlLastCell)

'put the rest of your code here

The in your copy range you want something like Range("B8:CC" & C.Row)