How to create a macro to copy and paste in the next empty row
Category: [Excel] Demo Available
How to create a macro to copy and paste in the next empty row
Requirement:
The user has a range of rows and wants to copy the last range and paste to the next empty row.
For example, in the attachment file, there is 3 set of data separate in three Sprints, anytime the user wants to add a newsprint would like to copy the last sprint and add it to the next empty row. The idea is to create a macro to automate it through a button.
This is the current VB macro code:
Sub NewSprint()
'
' NewSprint Macro
' including new sprint
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Range("A29:P40").Select
Selection.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
End Sub
Solution:
Code:
Sub NewSprint()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim findSprint As Range
Set findSprint = Range("A:A").Find("Sprint", After:=Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If Not findSprint Is Nothing Then
Range("A" & findSprint.Row & ":P" & LastRow).Copy Cells(LastRow + 2, 1)
End If
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro to auto delete file when passed 15 days |
| How to create a macro to move duplicates |
| How to merge tabs from different excel macro xlsm files into one file |
| How to create a macro to summarise data when new worksheet is created |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.