VBA Shift One Row Down.

[patricevease]

Hi all,

As the title states there is a section in my code where I am having a problem shifting the pasted data a row down below the previously pasted data.

Essentially the code will cycle through worksheets and select data to populate a table on "Sheet 2" by finding "Hello" on the worksheet and taking the range below it until there is "" (no cell value).

The actual problem comes from the last line "clientcounter = clientcounter + 1" - this pastes the data 1 column across, however I am trying to paste this 1 row down.

I have indented where I believe the problem stems from - this part of the code I think isn't actually necessary (I took this code from a similar one I had written).

If you know how to quickly fix this so that the data is pasted 1 row below please let me know!

Sub TablePopulate()


Dim sheetcount As Integer
Dim i As Integer


Sheets("Sheet2").Activate
Range(Cells(12, 2), Cells(16, 8)).ClearContents


sheetcount = ActiveWorkbook.Worksheets.Count


clientcounter = 2


For i = 1 To sheetcount


    If ActiveWorkbook.Sheets(i).Name <> "Sheet2" Then
    
        ActiveWorkbook.Sheets(i).Activate
        
        'Cells(2, 3).Activate
        'clientname = Cells(2, 3)
        
        'Cells(2, 3).Copy
        
        
        datarow_start = 1
        datarow_end = 1
        For j = 1 To 1000
        
            If Cells(j, 2) = "Hello" Then
            
                datarow_start = j + 1
            
                
                Exit For
                
            End If
        
        Next j
        
        If datarow_start <> 1 Then
        
            For g = datarow_start To datarow_start + 50
            
                If Cells(g, 2) = "" Then
                
                On Error Resume Next
                        
                    datarow_end = g - 1
                    Exit For
                    
                End If
            
            Next g
            
            Range(Cells(datarow_start, 2), Cells(datarow_end, 7)).Select
            Selection.Copy
        
        End If
        
        Sheets("Sheet2").Activate
        
        Cells(12, clientcounter) = clientname
        
        Cells(12, clientcounter).Select
        
        If datarow_start <> 1 Then
            Selection.PasteSpecial xlPasteValues
        End If
        
              
        clientcounter = clientcounter + 1
        
    
    End If


Next i




End Sub

Thanks in advance.