none
Run code to add rows above cell A1 RRS feed

  • Question

  • Hi,

    Have the follow code to add 4 rows if cell A1 on sheet "raw data file" when i paste value. It is not running, despite the code for add the rows working perfectly. Thank you so much in advance.

    'Object = Worksheet

    'Procedure = Change

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Target.Worksheet.Range("a1")) Is Nothing Then insertRow
    End Sub

    Module to add rows code:

    Sub insertRow()

        Dim rw As Long

        With ActiveCell
            rw = .Row
           ActiveCell.EntireRow.Resize(4).Insert Shift:=xlDown
        End With

    End Sub

    Thursday, October 31, 2019 11:42 AM

Answers

  • Thank you so much Hans
    • Marked as answer by BrunoClick Thursday, October 31, 2019 4:07 PM
    Thursday, October 31, 2019 4:07 PM
  • Running VBA code clears the clipboard in many situations; this is unavoidable...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by BrunoClick Friday, November 1, 2019 11:46 AM
    Thursday, October 31, 2019 8:23 PM

All replies

  • The code should be in the worksheet module of the relevant sheet, not in a standard module, and it should look like this to prevent the event from calling itself over and over again:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Target.Worksheet.Range("a1")) Is Nothing Then
            Application.EnableEvents = False
            insertRow
            Application.EnableEvents = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2019 2:12 PM
  • Thank you so much Hans
    • Marked as answer by BrunoClick Thursday, October 31, 2019 4:07 PM
    Thursday, October 31, 2019 4:07 PM
  • I have my file almost finished and noticed that cannot paste values to a sheet when in full screen mode. Code used to do full screen mode below. Investigated and looks like a bug, is this something you can help with please?

    Regards

    Thursday, October 31, 2019 4:09 PM
  • > Code used to do full screen mode below.

    Huh?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2019 4:46 PM
  • Sorry, code below:

    Private Sub workbook_activate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "show.toolbar(""ribbon"",false)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
    Application.ScreenUpdating = True
    End Sub

    Thursday, October 31, 2019 5:04 PM
  • Running VBA code clears the clipboard in many situations; this is unavoidable...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by BrunoClick Friday, November 1, 2019 11:46 AM
    Thursday, October 31, 2019 8:23 PM
  • Thank you Hans
    Friday, November 1, 2019 11:46 AM