copying values from range

[Jaye7]

Hi All,

I have been working on the following code which selects all the values in column A (rng1).

I then have it go to the last cell of the data in column D and select the next blank available row.

What I am stuck on as I want each value from the rng1 to be put in the next cell to the right in column D, so the first A1 value would go into the first available blank cell in column D, A2 value would go into E, A3 would go into F etc...

At the moment the script is just putting the last value of column A into the blank cell in column D.

Sub test1()
Dim rng1 As Range
 
Range("A" & Rows.Count).End(xlUp).Select
Set rng1 = Selection
Range("D" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
 
With rng1
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
End If
ActiveCell.Value = rng1.Value
 
ActiveCell.Offset(0, 1).Select
Next
End With
 
 
End Sub

 

[Dryver14]

try giving the offset a variable for instance

j = 1

ActiveCell.Value = rng1.Value

ActiveCell.Offset(0, j).Select
j = j + 1

 

[Jaye7]

Hi Dryver,

unfortunately it still does the same thing.

Sub test1()
Dim rng1 As Range
Range("A" & Rows.Count).End(xlUp).Select
Set rng1 = Selection
Range("D" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
 
With rng1
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
End If
j = 1
ActiveCell.Value = rng1.Value
ActiveCell.Offset(0, j).Select
j = j + 1
Next
End With
 
End Sub

 

[Dryver14]

yes I suppose it would as you have already given the cell a value try somrthing like

ActiveCell.Offset(0, j).value = rng1.value

and delete the line above

 

[AlphaFrog]

Sub test1()
 
Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, True
Application.CutCopyMode = True

End Sub

 

[Jaye7]

Thanks Alphafrog, once again you have come through, you make it all look so easy.