Copying a dynamic range to new worksheet

Hi Guys,

Very new to VBA/Macro's but this is the first time I haven't been able to solve an excel related problem without some creative googling so I hoped someone would be able to help me here.

Basically I need a macro that moves data (both text and values) to a new worksheet in the workbook. Usually I could just do this by recording a macro and copying down a few 100 cells. I can't, however, use this technique here because the table I am extracting information has other tables below it.

There is a blank row that separates the tables so I managed to write some code that finds the first blank cell from my starting point (B10 - which will always be the same). My thinking was that this blank row will always be there so if the macro knew to highlight upwards from the first blank cell until B10 and copy it into the new worksheet then I wouldn't have to do anything too complex. But I have no idea to make this work - maybe I need to use some kind of active cell command, but as I say I am completely new to VBA.

My code is:

Sub Find_Blank()
Dim BCell, NBCell

Range("B10").Select

For I = 10 To 100
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(I - 1)
NBCell = "B" & CStr(I - 2)
Exit Sub
Else
Range("B" & CStr(I + 1)).Select
End If
Next I

End Sub

I have attached the excel workbook as I'm not sure if I explained it particularly well. Basically I want to move the sites and totals in the first table to the sites column in the other worksheet but the number of sites will be variable. Any help would be much appreciated

Hi Richard,

Thanks for that it was very helpful and it worked. I really don't understand how the code actually works though (i.e. why range is A10:A when no information is either contained or pasted from these cells :s).

Is it possible to just copy one column of that table to the new worksheet because they will not always be in the same place and if I know how to copy and paste to new worksheet where the last cell copied will always be the one before the first blank one after B10 I think I will be able to work backwards and work out the logic behind it and apply it to new columns.So, for instance, how would I copy the site column to the new worksheet under the site heading? I tried to change the code you gave me in a number of different ways but had no luck.

I really need to do some reading on VBA before I try anything else but I wanted to sort this task out quickly . If anyone is able to help me with these issues it will be much appreciated

bump any ideas anyone?