I would like to copy a column's data (Example: A2:A10) to C2:C10. However, if C2:C10 already has data, I would like it to copy the data to the next column to the right. In this example that would be D2:D10.
How would I achieve this?
Add a comment
|
2 Answers
Option Explicit
Sub CopyCol()
Dim rngFrom As Range, rngTo As Range
With ActiveSheet ' or Sheets(1) or Sheets("Sheet1")
Set rngFrom = .Range("A2:A10")
Set rngTo = rngFrom.Offset(, 2)
Do While WorksheetFunction.CountA(rngTo) > 0
Set rngTo = rngTo.Offset(, 1)
Loop
rngFrom.Copy rngTo
End With
End Sub
Comments
Copy When Blank
Sub CopyWhenBlank()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
Dim srg As Range: Set srg = ws.Range("A2:A10")
Dim drg As Range: Set drg = srg.EntireRow.Columns("C")
Dim RowsCount As Long: RowsCount = srg.Rows.Count
Do While Application.CountBlank(drg) < RowsCount ' is not blank
Set drg = drg.Offset(, 1)
Loop
srg.Copy Destination:=drg
' Or to copy only values:
'drg.Value = srg.Value
MsgBox "Copied """ & srg.Address(0, 0) & """ to """ & drg.Address(0, 0) _
& """.", vbInformation
End Sub
