Paste to next available row VBA

[Kristofferson]

Hi all,

I'm very new to VBA and am struggling with a macro as per the subject line after searching on forums and playing with it all day so any help would be greatly appreciated thanks!

I have a colleague who will provide a monthly output to excel that I need to enter into a continual log. I have set up an Input page for this person to copy and paste into and would like to then transfer this data into the next available rows in a Log sheet. I have got as far as copying the data over but after trying many ways of setting the last available row to copy the data to with .End(xlUp) in various places within the code I still can't get it right. Any help would be gratefully received.

Code so far (without trying to paste to the last availabe row) is:

Sub CopyInputtoLog()

'Copies data pasted into Input sheet into Cases Log sheet and clears the input page

Dim shtInput As Worksheet, shtLog As Worksheet
Dim rngInput As Range, rngLog As Range

Set shtInput = ThisWorkbook.Worksheets("Input")
Set shtLog = ThisWorkbook.Worksheets("Cases Log")
Set rngInput = shtInput.Range("A2:N497")
Set rngLog = shtLog.Range("B4:O500")

rngInput.Copy
rngLog.PasteSpecial (xlPasteValues)

rngInput.ClearContents

shtInput.Range("$A$1").Value = "Paste as values here"

End Sub

(It's so small!)
Any direction would be great as I've searched the forums and none of the solutions seem to work for me. Apologies for the newbiness, go gentle, it's my first try at VBA!

Thanks very much.

 

[MARK858]

If you mean this line (and assuming you don't have headers as you are using A1)

shtInput.Range("$A$1").Value = "Paste as values here"

then the most common way would be

    If shtInput.Range("A1") = "" Then
        shtInput.Range("$A$1").PasteSpecial xlPasteValues
    Else
        shtInput.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If

Post a description of any issues that occur using the above

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Input").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Cases Log").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A2:N" & Lastrow).Copy
Sheets("Cases Log").Range("A" & Lastrowa).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Input").Range("A2:N" & Lastrow).ClearContents
Application.ScreenUpdating = True
End Sub

 

[MARK858]

If you mean these lines

Set rngLog = shtLog.Range("B4:O500")

 rngInput.Copy
 rngLog.PasteSpecial (xlPasteValues)

then if there is data in B3 try

Set rngLog = shtLog.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

 rngInput.Copy
 rngLog.PasteSpecial [COLOR="#FF0000"]xlPasteValues[/COLOR]

again post back with a description of any issues (including if there isn't data in B3)

 

[Kristofferson]

Thanks both. I had tried similar to both solutions but must have been going wrong somewhere and ended up overwriting the original data.

I went for Mark's second solution as the smallest change and works perfectly, thank you; however, I will certainly be including the Screen Update side step so thanks too MaiT!

 

[My Aswer Is This]

Glad we were able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks both. I had tried similar to both solutions but must have been going wrong somewhere and ended up overwriting the original data.

I went for Mark's second solution as the smallest change and works perfectly, thank you; however, I will certainly be including the Screen Update side step so thanks too MaiT!