Paste to next available row VBA

Kristofferson

New Member
Joined
Dec 2, 2016
Messages
2
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Code:
    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

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
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
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you mean these lines

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

 rngInput.Copy
 rngLog.PasteSpecial (xlPasteValues)

then if there is data in B3 try

Code:
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)
 
Last edited:

Kristofferson

New Member
Joined
Dec 2, 2016
Messages
2
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top