+ Reply to Thread
Results 1 to 4 of 4

Insert rows instead of copying rows

Hybrid View

  1. #1
    Registered User
    Join Date
    03-08-2009
    Location
    Holland
    MS-Off Ver
    Excel 2007
    Posts
    25

    Insert rows instead of copying rows

    I am looking for help with a macro who is coping a "named range" from a sheet that contains a large list of named ranges.

    The active sheet has 20 or more named ranges. This list can grow and shrink.

    The macro below is everything what I need, but, the macro is not moving data but overwriting other data.

    When the named range is copied it will need to move and insert the data in the active sheet, not overwriting.

    For testing purposes I created 3 name ranges: "ABC", "DEF" and "GHI".
    (attachment is anonymised)

    Is that possible?





    Private Sub CommandButton1_Click()
    
        If ListBox1.ListIndex = -1 Then Exit Sub
        Dim cell As Variant
        On Error Resume Next
        Set cell = Application.InputBox("Choose a destination cell to paste ", "Input Destination", Type:=8)
        If cell Is Nothing Then Exit Sub
        Range(ListBox1.List(ListBox1.ListIndex)).Copy cell
      
    End Sub
    
    Private Sub UserForm_Activate()
        
        Dim i As Long, refer As Variant, ss As Variant
        For i = 1 To ActiveWorkbook.Names.count
            refer = ActiveWorkbook.Names(i).Name
            If Not IsError(refer) Then
                If Left(refer, 2) <> "_x" Then
                    ss = ActiveWorkbook.Names(i).RefersTo
                    If InStr(1, ss, "ProjectSchedule", vbTextCompare) > 0 Then
                        ListBox1.AddItem refer
                    End If
                End If
            End If
        Next
    
    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Insert rows instead of copying rows

    Private Sub CommandButton1_Click()
    
        If ListBox1.ListIndex = -1 Then Exit Sub
        Dim cell As Variant
        On Error Resume Next
        Set cell = Application.InputBox("Choose a destination cell to paste ", "Input Destination", Type:=8)
        If cell Is Nothing Then Exit Sub
        'Range(ListBox1.List(ListBox1.ListIndex)).Copy cell
        Range(ListBox1.Value).Copy
        cell.Insert Shift:=xlDown
      
    End Sub
    Since I don't know how do you want to insert and how the user select the range,
    so the things which I tested are :
    - Choose ABC from the ListBox
    - Choose cell A13 for the InputBox.
    Then the macro copy the ABC named range and then insert the copied cell starts from cell A13.

    The image below, I choose range DEF and choose cell A8
    2021-09-21_00-43-26.gif
    Last edited by karmapala; 09-20-2021 at 12:45 PM.

  3. #3
    Registered User
    Join Date
    03-08-2009
    Location
    Holland
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Insert rows instead of copying rows

    Hello Karmapala,

    Thanks for looking into my "problem".

    I change
    Range(ListBox1.Value).Copy
    into

    Range(ListBox1.Value).Cut
    and thats all what I need.



    Thanks, my "problem" is solved.

    Henk
    Last edited by Henk; 09-20-2021 at 01:11 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Insert rows instead of copying rows

    Thank you, Henk.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Insert rows above sum row copying the string formulas from the row above
    By dache416 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-21-2018, 01:16 PM
  2. [SOLVED] VBA to insert value into column rows before copying to new sheet
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-08-2016, 01:21 PM
  3. Insert rows with copying formula from above row
    By masta-s in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-07-2012, 09:09 AM
  4. [SOLVED] Insert new rows with copying formulas but having empty cells
    By juskojj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-04-2012, 01:55 PM
  5. Insert Rows And Fill Formulas Copying rows
    By Mj_McKenzie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-25-2012, 03:18 PM
  6. Auto-insert rows & contextual copying
    By Brendon Green in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-06-2009, 05:35 PM
  7. Replies: 3
    Last Post: 09-14-2005, 05:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1