louvalterry
10-28-2013, 01:36 PM
I have been looking for a solution to my problem now for a couple of days. I need some VBA Excel code the finds the first blank row in my sheet. I then want to pastespecial a row from another sheet which I copied. The code I found (Attached) pastes the new row at the very bottom of the sheet (row 1048576) and then upwards. This is not what I need. I need to paste in on row 10 if rows 1 - 9 already have data.
/ Begin Code
Private Sub cmdDeleteRow_Click()
Dim ws As Worksheet
Dim lRow As Long
Dim LastRow As Long
If Not IsInRange(ActiveCell, ActiveSheet.Range("ForecastTable")) Then
MsgBox "You can not delete lines here.", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
ActiveSheet.Unprotect
MsgBox "You are about to copy the deleted data to the Audit tab "
ActiveCell.EntireRow.Copy
ActiveSheet.Unprotect
Sheets("Audit").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Audit").Range("A" & LastRow).PasteSpecial xlPasteValues
Sheets("Monthly Staffing_F").Select
ActiveCell.EntireRow.Delete
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub
/ End Code
/ Begin Code
Private Sub cmdDeleteRow_Click()
Dim ws As Worksheet
Dim lRow As Long
Dim LastRow As Long
If Not IsInRange(ActiveCell, ActiveSheet.Range("ForecastTable")) Then
MsgBox "You can not delete lines here.", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
ActiveSheet.Unprotect
MsgBox "You are about to copy the deleted data to the Audit tab "
ActiveCell.EntireRow.Copy
ActiveSheet.Unprotect
Sheets("Audit").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Audit").Range("A" & LastRow).PasteSpecial xlPasteValues
Sheets("Monthly Staffing_F").Select
ActiveCell.EntireRow.Delete
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub
/ End Code