VBA To Copy available data to next blank row

[BMAK]

Hi,
I have some code that I use to copy a defined range from a closed workbook to a range in the open workbook, also at the end it takes the data and fills any blank cells with the data above for a specified range.

What I wish to do is
1. Have the macro only copy cells that have data so the range highlighted in red would go from A8 to column P and down to the next blank row.
2. Have the macro paste the data to the next blank row in the open workbook (Highlighted in orange).
3. Have the fill down part of the macro continue until the entire row is blank as i currently have only certain cells contained in data that are blank but never the entire row, so a blank row would denote the end of the data. Current range is highlighted in blue.

So I am asking if anyone can help me specify a range from a cell until a blankrow, I have tried using the endxlUP/down functions but with no success

Thanks
Brian.

Private Sub CopyData_AfterUpdate()
 Dim wbSource As Workbook, wbTarget As Workbook
    Dim rCopy  As Range
    Dim tPath  As String
    Dim Month As String
    Dim Filldata As Range
    Dim dest As Range
    Dim X As Variant
     With Application
        .ScreenUpdating = False
        On Error Resume Next
        Set wbSource = Workbooks.Open("C:\SourceData.xls", True) 'Sets source of information.
        wbSource.Worksheets("SourceData").Range("A8:P92").Copy ' Copys range 
        wbSource.Close False ' close the source workbook without  saving  changes
        Application.GoTo Worksheets("Sheet3").Range("B8"), True   'Selects range to copy data to
        ActiveSheet.PasteSpecial ' Pastes Data
        Set wbSource = Nothing
        Set wbTarget = Nothing
        Set rCopy = Nothing
        .ScreenUpdating = True
        .CutCopyMode = False 'clear Clipboard
        End With
 
 
        Sheets("Sheet3").Select ' Selects range to fill down
       Filldata = Range("A8:O93").Select
 
            For Each X In Selection.Cells
                If X.Text = "" Then
                X.Value = X.Offset(-1, 0).Value
            End If
            Next X ' IF statement fills each cell until new value then fills that value for entire range.
 
End Sub

 

[Datsmart]

Made some adjustments to your code to reference LastRows in Source and Target ranges.
It worked in my test workbook, but if you have merged cells, or any unusual data setup it might work differently. Post back if you have questions.

Private Sub CopyData_AfterUpdate()
Dim wbSource As Workbook, wbTarget As Workbook
Dim rCopy  As Range
Dim tPath  As String
Dim Month As String
Dim Filldata As Range
Dim dest As Range
Dim X As Variant
With Application
    .ScreenUpdating = False
    On Error Resume Next
    Set wbTarget = ActiveWorkbook
    Set wbSource = Workbooks.Open("C:\~~~\SourceData.xls", True) 'Sets source of information.
    SourceLastRow = wbSource.Worksheets("SourceData").Range("A65536").End(xlUp).Row
    TargetLastRow = wbTarget.Worksheets("Sheet3").Range("A65536").End(xlUp).Row + 1
    
    'Copy Source to Target in one step
    wbSource.Worksheets("SourceData").Range("A8:P" & LastRow).Copy _
    wbTarget.Worksheets("Sheet3").Range("B" & TargetLastRow)
    
    'Close Source without Save
    wbSource.Close False
    .ScreenUpdating = True
    .CutCopyMode = False 'clear Clipboard
End With
 
'Set FillData range
LastRow = Sheets("Sheet3").Range("A65536").End(xlUp).Row
Set Filldata = Sheets("Sheet3").Range("A8:O" & LastRow)
For Each X In Filldata
    If X.Text = "" Then
        X.Value = X.Offset(-1, 0).Value
    End If
Next X ' IF statement fills each cell until new value then fills that value for entire range.

Set wbSource = Nothing
Set wbTarget = Nothing

End Sub