Jul 11 2022 01:46 AM
Hi,
I have a macro to add a new row, but now I want to paste the value from the above row.
My code:
Private Sub CommandButton1_Click()
Dim rowNum As Integer
On Error Resume Next
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
Title:="Add row", Type:=1)
Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
End Sub
For example, I add a row on row 6. Here I want the date from the row above in the table:
Jul 11 2022 02:27 AM
Try this:
Private Sub CommandButton1_Click()
Dim rowNum As Long
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
Title:="Add row", Type:=1)
If rowNum > 0 Then
Rows(rowNum).Copy
Rows(rowNum).Insert
Application.CutCopyMode = False
End If
End Sub
Jul 11 2022 02:59 AM
Jul 11 2022 03:20 AM
SolutionHow about this:
Private Sub CommandButton1_Click()
Dim tbl As ListObject
Dim row1 As Long
Dim row2 As Long
Dim rowNum As Long
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
Title:="Add row", Type:=1)
Set tbl = Me.ListObjects(1)
row1 = tbl.Range.Row
row2 = row1 + tbl.Range.Rows.Count - 1
If rowNum <= row1 + 1 Or rowNum > row2 + 1 Then
MsgBox "Row number not valid for table! Please try again.", vbExclamation
Else
tbl.ListRows.Add Position:=rowNum - row1
Cells(rowNum, 1).Value = Cells(rowNum - 1, 1).Value
End If
End Sub
Jul 11 2022 03:20 AM
SolutionHow about this:
Private Sub CommandButton1_Click()
Dim tbl As ListObject
Dim row1 As Long
Dim row2 As Long
Dim rowNum As Long
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
Title:="Add row", Type:=1)
Set tbl = Me.ListObjects(1)
row1 = tbl.Range.Row
row2 = row1 + tbl.Range.Rows.Count - 1
If rowNum <= row1 + 1 Or rowNum > row2 + 1 Then
MsgBox "Row number not valid for table! Please try again.", vbExclamation
Else
tbl.ListRows.Add Position:=rowNum - row1
Cells(rowNum, 1).Value = Cells(rowNum - 1, 1).Value
End If
End Sub