PDA

View Full Version : Copy & paste in next available cell



marcop33
11-14-2012, 08:49 AM
I'm new to VBA and would like some help in modifying a VBA code to past in the next available cell below. the current code is the following:

Sub COPY()

Sheets("Fattura").Select
Range("H38").Select
Selection.COPY

Sheets("Log fatture").Select
Range("F5").Select
Sheets("Log fatture").Range("F5").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F5").Select


End Sub

GreenDR
11-14-2012, 10:03 AM
Dim r As Long, c As Long

Selection.Copy 'copy the current cell
r = Selection.Row 'get the current row
c = Selection.Column 'get the current column

'get the next available cell below the active cell
If Cells(r + 1, c).Value <> "" Then 'if below cell is not empty
Cells(r, c).End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues) 'find the next available cell in the column
Else 'if below cell is empty
Cells(r, c).Offset(1, 0).PasteSpecial (xlPasteValues) 'paste the value in the below cell
End If
Application.CutCopyMode = False 'esc

marcop33
11-14-2012, 10:56 AM
Thanks for the prompt reply.
I've tried intgrating your code but am still having some problems...

Sub COPY()

Sheets("Fattura").Select
Range("H34").Select
Selection.COPY

Sheets("Log fatture").Select
Range("F5").Select

If Cells(5 + 1, F).Value <> "" Then 'if below cell is not empty
Cells(5, F).End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues) 'find the next available cell in the column
Else 'if below cell is empty
Cells(5, F).Offset(1, 0).PasteSpecial (xlPasteValues) 'paste the value in the below cell
End If

Sheets("Log fatture").Range("F5").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F5").Select


End Sub

GreenDR
11-14-2012, 11:04 AM
the problem is that you cant use F directly in the cells(), you either need to use a number or a variable.

i have modified the Cells() to Range() for your convenience


Sub COPY()

Sheets("Fattura").Select
Range("H34").Select
Selection.COPY

Sheets("Log fatture").Select
Range("F5").Select

If Range("F6").Value <> "" Then 'if below cell is not empty
Range("F5").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues) 'find the next available cell in the column
Else 'if below cell is empty
Range("F5").Offset(1, 0).PasteSpecial (xlPasteValues) 'paste the value in the below cell
End If

Sheets("Log fatture").Range("F5").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F5").Select


End Sub

marcop33
11-14-2012, 11:15 AM
thanks once again. I'm getting closer, but do not understadn why now it copies the same value in both cell F5 and F6. it copies over the value in F5 as well as writing in the nxt available cell.