+ Reply to Thread
Results 1 to 12 of 12

Insert Rows Automatically...

Hybrid View

  1. 11-09-2011, 11:50 AM #1
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Insert Rows Automatically...

    Hi all,

    This may seem like a relatively easy question but I'm getting nowhere fast so thought I'd ask here.

    I wish to have a new row automatically created when the last piece of data is entered into cell H11 for instance.
    My row (H1:11) is made up of drop-down boxes and cells that refer to vlookups... and when a selection is made from the drop-down box in cell H11... I want a new row to be created underneath... This also needs to run on any consecutive rows that are created as a result of this... so... when the selection is made from H12, again, a new row is created and so on.

    Hope you can help.

    Gavin
    Reply With QuoteRegister To Reply
  2. 11-09-2011, 11:55 AM #2
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically...

    Put this code in the code page for the worksheet that you want this to happen on:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns(8)) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Target.Value <> "" Then
                Me.Rows(Target.Row + 1).Insert
            End If
        End If
    End If

End Sub
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
  3. 11-09-2011, 03:35 PM #3
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert Rows Automatically...

    Quote Originally Posted by davegugg View Post
    Put this code in the code page for the worksheet that you want this to happen on:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns(8)) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Target.Value <> "" Then
                Me.Rows(Target.Row + 1).Insert
            End If
        End If
    End If

End Sub
    Fantastic!! Thank you so much! Works a treat!
    Is there any way I can make the highlighted cell become A12 rather than H12 once the new row has been inserted?
    Reply With QuoteRegister To Reply
  4. 11-09-2011, 03:45 PM #4
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically...

    Just do Cells(12,"A").Activate
    Reply With QuoteRegister To Reply
  5. 11-09-2011, 04:12 PM #5
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert Rows Automatically...

    Quote Originally Posted by davegugg View Post
    Just do Cells(12,"A").Activate
    Thanks, that's great!! Although... I realised my mistake once I added the code...
    I don't actually want A12 activated... I want the next cell in column A activated... So... When the last selection is made from column H... Say for instance H2, I'd like A3 to be selected and then when H3 has been changed... I'd like A4 to be activated and so on...

    Hope that makes sense?
    Reply With QuoteRegister To Reply
  6. 11-09-2011, 04:44 PM #6
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically... 

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns(8)) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Target.Value <> "" Then
                Me.Rows(Target.Row + 1).Insert
                Me.Cells(Target.Row+1,1).Activate
            End If
        End If
    End If

End Sub
    Please refrain from quoting whole posts - it clutters the forum (see rule 12). I can easily look at the previous post to see what was last written.
    Reply With QuoteRegister To Reply
  7. 11-09-2011, 05:05 PM #7
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert Rows Automatically...

    Brilliant! Thank you!

    I have one more question... Sorry... I'm a complete newbie when it comes to VB and Excel.

    I would like rows copied to seperate sheets within the same workbook... I have 25 sheets in total, each named with a room number... Ideally... I'd like a row which has information about the room G8 copied to the sheet G8 once the last selection has been made in column H. The same goes for any other rooms also. The idea is that each sheet will have a constantly growing list of entries for that particular room.
    Reply With QuoteRegister To Reply
  8. 11-09-2011, 05:42 PM #8
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically...

    You'll need to change where it says "Column Letter" to the column that has the sheet name in it. For example, if the sheet name in each row is in column C, change it to "C". It needs to be changed in both places.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns(8)) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Target.Value <> "" Then
                Me.Rows(Target.Row + 1).Insert
                Me.Range(Me.Cells(Target.Row, 1), Me.Cells(Target.Row, 8)).Copy Destination:=Sheets(Me.Cells(Target.Row, "Column Letter").Value).Cells(Sheets(Me.Cells(Target.Row, "Column Letter").Value).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
                Application.CutCopyMode = False
                Me.Cells(Target.Row + 1, 1).Activate
            End If
        End If
    End If

End Sub
    Reply With QuoteRegister To Reply
  9. 11-09-2011, 06:18 PM #9
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert Rows Automatically...

    Thanks Dave!! Works perfectly!!

    The cells on the master sheet are mainly drop-down boxes... Is there any way to copy these with just the values and not the list box arrow...

    Thanks again for your help.

    Gavin
    Reply With QuoteRegister To Reply
  10. 11-09-2011, 06:30 PM #10
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically...

    Not while pasting. You'd have to clear off the data validation on those cells separately.
    Reply With QuoteRegister To Reply
  11. 11-10-2011, 04:18 AM #11
    GeekyGav
    GeekyGav is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert Rows Automatically...

    Okay, no problem! Thanks!!

    I have a vlookup in column B which works perfectly well on the master sheet but when the data is automatically copied over... The information in column B isn't written and I get a #REF!
    in the cell...
    When I click on the cell to see what's going on... This is what I see in the formula bar... =VLOOKUP(A2,DataSource!#REF!,2,FALSE)

    I guess the vlookup formula isn't being copied over??

    Also... When a new row is inserted automatically below on the master sheet, the vlook formula isn't being copied down either.

    Any ideas?
    Reply With QuoteRegister To Reply
  12. 11-16-2011, 10:56 AM #12
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Insert Rows Automatically...

    In reference to the vlookup #Ref problem: Usually if you copy a formula to a new sheet, it will keep the same number of columns between your formula and its target. For example, if on cell D1 of sheet 1, you have a simple formula that says =A1, and you copy that cell and paste it to sheet 2 cell C1, it will try to make the formula refer to the column before A, which does not exist (A is the first column). I hope that makes sense. To counter this effect, you can make the cell references in the formula absolute (which would be a good idea when using vlookup anyway.) With the cell address in the formula highlighted, press F4. The formula address will change from DataSource!A1:D10 to DataSource!$A$1:$D$10.

    In reference to your other issue, Excel never assumes you want the formulas copied into a newly inserted row. You will either need to do that manually, or create a separate sub for that.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1