Do you want to copy from T2 to AC2 and paste into the next open line, then continue to do so until Coulmn A is empty

advertisements

What I have is some data in Column A, from rows 1 to some unknown end row. I have some data in row 2 Column T through AC that I want to copy into any open cells in those same rows, and then stop pasting when looking at Row A and seeing the first blank row.

I have the code to copy, find the next open row in T and paste once. What I need is to figure out how to loop until Row A has the first blank cell.

Sub Find_next_BLANK_A_andPaste()
'
' Find_next_BLANK_A_andPaste Macro
'

  Range("T2:AC2").Select
    Selection.Copy
' Copy the formulas in T2 to AC2

    Range("T1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

'
End Sub


I usually reference cells in VBA using the cells property with index numbers, so that's how I have it in the sub below. I'm sure the code below could be modified to use A1 notation instead. But here's how I would do it:

Sub Find_next_BLANK_A_andPaste()

    Dim CurrentRow As Long
    Dim CurrentWS As Worksheet

    'Set the current worksheet as a variable just so it can be referenced
    'easier. If your worksheet isn't the first worksheet of the workbook,
    'you'll need to replace Worksheets(1) below with the proper reference
    'to your sheet (i.e. Worksheets(2) or Worksheets("Sheet3"))
    Set CurrentWS = ActiveWorkbook.Worksheets(1)

    With CurrentWS

        'Start at row 1
        CurrentRow = 1

        'Loop until the cell in column A of the next row is blank
        While .Cells(CurrentRow + 1, 1) <> ""

            'Copy everything from column T to column AC of the current row
            .Range(.Cells(CurrentRow, 20), .Cells(CurrentRow, 29)).Copy

            'Select column T of the next row
            .Cells(CurrentRow + 1, 20).Select

            'Paste the copied data
            ActiveSheet.Paste

            'Increment the CurrentRow variable to check the next row
            CurrentRow = CurrentRow + 1

        Wend

    End With

End Sub

This assumes the first blank cell we find in column A is the "end". If there could be blank cells in column A before we've actually reached the end of the data in column A we'll have to modify the above code a little.