VBA to copy & paste only cell with value in loop

[ALAMZEB]

hi I am new with macros so above VBA could be entirely wrong
I want VBA to copy from column E only cells that have value and than paster values to column D
if any cells have no value than it shouldnt copy

Sub test2()
Sheets(Array("Sheet3", "Sheet2")).Select
  Range("E5").Select
  Do While ActiveCell.Value <> ""
  'ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
  Selection.Copy
  
  
 
  Range("d5").Select
  Selection.PasteSpecial Paste:=xlPasteValues
  Range("E5").Select
  'ActiveCell.Offset(0, 2).Select
  ActiveCell.Offset(1, 0).Select
  Loop
End Sub

 

[Hui]

Try this:

Sub test2()
Dim LastRowD As Integer, LastrowE As Integer

Sheets("Sheet1").Select
LastrowE = Range("E" & Rows.Count).End(xlUp).Row
 
For i = 1 To LastrowE
  If Cells(i, 5).Text <> "" Then
  LastRowD = Range("D" & Rows.Count).End(xlUp).Row
  Cells(i, 5).Copy Cells(LastRowD + 1, 4)
  End If
Next i
End Sub

 

[ALAMZEB]

Thanks HUI

but it works but i want to paste value in column which yiu did but right agaisnt cell of column E
for example
if E7 has value than copy and past value in D7 only

Thanks in advance

 

[Hui]

Sub test2()
Dim LastrowE As Integer

Sheets("Sheet1").Select
LastrowE = Range("E" & Rows.Count).End(xlUp).Row

For i = 1 To LastrowE
  If Cells(i, 5).Text <> "" Then
  Cells(i, 5).Copy Cells(LastRowE, 4)
  End If
Next i
End Sub

 

[ALAMZEB]

Hi HUI


not its copying only last value of column E to last cell of column D

 

[Nebu]

Hi try changing this to

Cells(i, 5).Copy Cells(LastRowE, 4)

Cells(i, 5).Copy Cells(i, 4)

Thanks

 

[ALAMZEB]

hi NEbU

that works great but i need to only paste values, theer would be formulas in E but i need values only in D

 

[Nebu]

Hi:

Change the following code:

 Cells(i, 5).Copy Cells(LastRowE, 4)

Cells(i, 5).Copy
Cells(i, 4).PasteSpecial xlValues

Thanks

 

[Hui]

Thanx Nebu

 

[ALAMZEB]

perfect
Thankyo

 

[Nebu]

@Hui and Alam ur welcome