CODEWITHSUNDEEP
excelvba
TSP
TSP

Reputation: 1

Want to Copy from T2 through AC2 and Paste down into the next open row, and then keep doing that until Coulmn A is blank

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

Upvotes: 0

Views: 93

Answers (2)

Amorpheuses
Amorpheuses

Reputation: 1423

If you're looking to copy from row 3 all the way to the last row in column "A", you can do this:

Sub CopyAll()
  Dim lastRow As Integer
  With Sheets(1)
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    .Range("T2:AC2").Copy Destination:=.Range("T3:AC" & lastRow)
  End With
End Sub

But you mention that you want to copy "T2:AC2" into any open cells in the range given above, then you're more likely after this:

Sub CopyOnlyToBlankRows()
  With Sheets(1):
    Dim lastRow As Integer
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).row

    Dim rng As Range
    Dim r As Integer
    For r = 2 To lastRow:
      Set rng = .Cells(r, "T").Resize(, 9)
      If WorksheetFunction.CountA(rng) = 0 Then
        .Range("T2:AC2").Copy Destination:=rng
      End If
    Next
  End With
End Sub

What it will do is to only copy T2:AC2 to a row when there's nothing in columns T to AC of that row.

Upvotes: 0

Josh2566
Josh2566

Reputation: 38

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.

Upvotes: 0

Related Questions