Copy Data Down till the next blank in column C

[JamesHough97]

Hi guys,

I am creating a macro and I am stuck at copying over the store number/Floor and matching up to the correct data.
This is what I have now
Sub Get_Store()
Dim x As Integer
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("CADout") '<== Sheet that has raw data
Dim LRow As Long, Found As Range

Set Found = ws.Range("A2:Z1000000").Find("HANDLE") '<== Header name to search for

If Not Found Is Nothing Then
Found.Select
Range(ActiveCell, ActiveCell.Offset(0, 10)).Select
Selection.Clear

End If

' Set numrows = number of rows of data.
Application.Goto Reference:="Store_list"
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count

' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows

Application.Goto Reference:="Store_List"
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Selection.Copy
Sheets("CADout").Select

Lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & Lastrow).PasteSpecial xlPasteValues


Selection.AutoFill Destination:=Range("A2:A" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select


'Move to next store - rename range names
Sheets("store_Data").Select
Application.Goto Reference:="Store_List"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Name = "Store_List"

Next


End Sub

The First part is to create the blank and then I can't get it to fill until that point
Any help would be appreciated.

Thanks,
James

 

[DanteAmor]

Hi and welcome to MrExcel

Better explain how your sheets are.
What do you want to copy and where do you want to paste it.
To show samples of your data use XL2BB tool, look at my signature.