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
Bookmarks