Results 1 to 4 of 4

Insert rows instead of copying rows

Threaded 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

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. [SOLVED] Same formula in each cell of column; insert rows w/o copying down
    By tgdavis in forum Excel General
    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