2

I have a sheet where the number of rows is dynamic. I am trying to add a macro which adds a new row after each active row and it should add the text "No Show" under column C of each new row added and the it should add the cell value E5 under D column.

Here is the example below:

Current Sheet:

enter image description here

After the Macro: (Test in E5 is Holiday)

enter image description here

I have a macro to add new empty rows but not sure how to integrate the other pieces of it.

Sub Insert_Blank_Rows()

Selection.End(xlDown).Select
Do Until ActiveCell.Row = 1
    ActiveCell.EntireRow.Insert shift:=xlDown
    ActiveCell.Offset(-1, 0).Select
Loop

End Sub
Mesut Akcan
  • 899
  • 7
  • 19
TA Arjun
  • 25
  • 4

3 Answers3

1
Sub FFF()
    Dim r&, vE5
    vE5 = [E5]: r = Cells(Rows.Count, 1).End(xlUp).Row + 1
    While r > 1
        Rows(r).Insert
        Cells(r, 1).Resize(, 4) = Array(Cells(r - 1, 1).Resize(, 2), "No Show", vE5)
        r = r - 1
    Wend
End Sub
JohnyL
  • 6,894
  • 3
  • 22
  • 41
  • Thanks for the link...can you comment your code? I would like to understand some row of the code...it is new for me...thanks...example the row where is vE5=[E5]...... – Ferdinando Dec 08 '18 at 12:55
  • 1
    `vE5 = [E5]` means "get value of range E5 and assign to vE5 variable". Since I don't know how many rows you have, I store the value of `E5` range in variable. Then I just go from down to top. When I insert row, the data to be copied is one row less than inserted one - hence "-1" from current row `r`. – JohnyL Dec 08 '18 at 13:05
  • i tried your code but doesn't "work" as asked...There aren't into column A and B the data. no show and holiday are write into no correct cells...you have to update your code if you want answer the question... – Ferdinando Dec 09 '18 at 08:00
0

if i understood your question you can:

in this example i suppose that you have in cell E5 the text Holiday.

i tried at no change your code

EDITED THE IMAGE and CODE

(because before i used E1 cell and i don't write AB... into new column)

BEFORE EXECUTE THE MACRO

enter image description here

AFTER MACRO

enter image description here

Sub Insert_Blank_Rows()
Dim text, textCell_E5 As String
Dim myRow As Long

text = "no Show" ' this thext goes into column C
textCell_E5 = Cells(5, 5) ' Holiday
ActiveSheet.Range("A1").Select ' or cells(1,1).Activate

Selection.End(xlDown).Select
myRow = ActiveCell.Row + 1
Cells(myRow, 1).Offset(0, 2) = text
Cells(myRow, 1).Offset(0, 3) = textCell_E5
Cells(myRow, 1).Offset(0, 0) = Cells(myRow, 1).Offset(-1, 0) 
Cells(myRow, 1).Offset(0, 1) = Cells(myRow, 1).Offset(-1, 1)

  Do Until ActiveCell.Row = 1

      ActiveCell.EntireRow.Insert shift:=xlDown

      myRow = ActiveCell.Row ' get the current row
      Cells(myRow, 1).Offset(0, 2) = text ' write into column C the no Show
      Cells(myRow, 1).Offset(0, 3) = textCell_E5 ' add Holiday Text
      Cells(myRow, 1).Offset(0, 0) = Cells(myRow, 1).Offset(-1, 0) 'write into column A (new row)
      Cells(myRow, 1).Offset(0, 1) = Cells(myRow, 1).Offset(-1, 1) ' write into column B (new row)

      ActiveCell.Offset(-1, 0).Select
  Loop

End Sub

I Tried the code and works.

Hope this helps

Ferdinando
  • 964
  • 1
  • 12
  • 23
  • [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – JohnyL Dec 08 '18 at 09:39
0

loop backwards:

Option Explicit

Sub Insert_Blank_Rows()
    Dim iRow As Long
    Dim myText As String

    myText = Range("E5").Text
    With Selection
        For iRow = .Rows.Count To 1 Step -1
            .Rows(iRow + 1).EntireRow.Insert shift:=xlDown
            With .Rows(iRow + 1)
                .Range("A1:B1").Value = .Offset(-1).Range("A1:B1").Value
                .Range("C1:D1").Value = Array("No Show", myText)
            End With
        Next
    End With
End Sub
DisplayName
  • 13,283
  • 2
  • 11
  • 19