0

Hi have code to add new row. When I click the button, the code will run to add new row based on what I set from coding. Now, what I want is when I click the button, row from Sheet 1 & row from Sheet 2 will be added together. I placed the button at Sheet 1.

What item I need to add so that the button that I placed in Sheet 1 work for both Sheet 1 & Sheet 2 automatic. Example, if I add 3 new row, Sheet 1 & Sheet 2 will add new 3 row as well.

This is my code.

    Sub RectangleRoundedCorners7_Click()
    Dim tmpStr As String, howMany As Integer
Dim i As Long
    tmpStr = InputBox("How many row do you want to add?                                                                                       {Reminder: not to add row if lowest cell still blank}")
        If Len(tmpStr) = 0 Then
        howMany = 0
    Else
        howMany = CInt(tmpStr)
    End If
    With Range("B7").End(xlDown).EntireRow
        For i = 1 To howMany
              .Copy
            .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
             Application.CutCopyMode = False
      On Error GoTo 0
        Next
    End With
End Sub

Thank You.

braX
  • 11,506
  • 5
  • 20
  • 33
Wafee89
  • 21
  • 3
  • `Range` is a property of a `Sheet` so instead of using the generic `Range` - which refers to the active sheet, use the explicit sheet e.g. `ThisWorkbook.Sheets(1).Range` and `ThisWorkbook.Sheets(2).Range` – CHill60 Aug 18 '23 at 08:50
  • Hi @CHill60 from my coding, where should i put that? is it replacing Range("B7") with ThisWorkbook.Sheets(4).Range ("B7")and ThisWorkbook.Sheets(5).Range ("B7") ? Sorry I totally new with VBA. – Wafee89 Aug 18 '23 at 09:21
  • Probably - your code doesn't do anything when I run it so it is obviously dependant on the data you have in your workbook. You're not adding "new" rows, you are copy/pasting data. I'm also not sure why you have `On Error GoTo 0` in the middle of your sub. Finally, why not use a button instead of a rectangle? – CHill60 Aug 18 '23 at 13:16

1 Answers1

0

Wrap existing Sheet1 code in a loop to also process Sheet2 identically.

Sub RectangleRoundedCorners7_Click()
    Dim tmpStr As String, howMany As Integer
    Dim i As Long
    Dim Sht as Variant
    tmpStr = InputBox("How many row do you want to add?)                                                                                       {Reminder: not to add row if lowest cell still blank}")
    If Len(tmpStr) = 0 Then
        howMany = 0
    Else
        howMany = CInt(tmpStr)
    End If
    For Each Sht In Array("Sheet1", "Sheet2")
        With Sheets(Sht).Range("B7").End(xlDown).EntireRow
            For i = 1 To howMany
                Application.CutCopyMode = False
                .Copy
                .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
            Next
        End With
    Next
End Sub
taller_ExcelHome
  • 2,232
  • 1
  • 2
  • 12