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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,468
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
18,714
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,468
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
18,714
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,489
Messages
5,837,640
Members
430,506
Latest member
TonyIbbs

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