Replica Dynamic Named Range Using a Macro

Hi,

Yestesday, I posted this question in the 'Excel Formulas & Functions' Forum, hpoing that someone might have a resolution for my issue using either a Formula or a Function. So far, I haven't got a resolution for this.

So, given that I am open to having macros in my Workbook and I'm a bit pressed for time, I thought I'd pose the same question in this Forum. Perhaps there's a way of updating a 'Named Range' with additional rows in one worksheet from another dynamic Named Range in another Worksheet. My situation is as follows:

I have a range of cells that is 10 columns by 5 rows on an 'Entry' Worksheet where I request input from users of 'Task' information (like a Project Tasks).

My need is for the users to be able to copy any of the rows within that range and then paste additional rows, so that they can add any number of additional Tasks.

I have named the range of 10 columns by 5 rows as 'Tasks' and when I copy and paste in additional rows within that same range, I see that the range in the 'Name Manager' extends to included the additional rows. This is exactly what I need.

However, I now need to write the rows, including any additionally added rows, to a separate 'Dataset' Worksheet for backend processing.

Does anyone know how to replicate a dynamically expanding Named Range to a separate Worksheet?

To be clear, I've attempted to get this working, but so far without success.

I've created a separate Named Range called 'Task_Data' in the 'Dataset' Worksheet of 10 columns by 5 rows. Then I used the technique of selecting that range, entering the formula '=Tasks' and using the [Shift] + [Ctrl] + [Enter] key combination to create an array reference back to the 'Tasks' Name Range in the 'Entry' Worksheet.

This works with the initial Named Range, but as soon as I insert extra rows into the 'Tasks' Named Range, they seem to push the bottom row(s) of Tasks outside of the 'Task_Data' Named Range in the 'Dataset' Worksheet.

Any suggestions on how to get the 'Dataset' Worksheet Named Range of 'Task_Data' to automatically expand to include additional rows that are added to the 'Tasks' Named Range would be most appreciated.

Cheers,

Wayne

Hi Sean,

Thanks very much for the prompt response to my question.

I shell try this out and let you know how it works out.

Cheers,

Wayne

Hi Sean,

Thanks again for the prompt feedback.

I have a couple of commitments this afternoon, but will get back to this during the evening and try out the change you have provided. I'll be able to provide a Workbook with what I am attempting at that time as well.

Cheers,

Wayne

Hi Sean,

As per my previous post, I tried your updated code this evening, but again ran into an error, this time 'Object required' on the following line:

Set MyRange = Entry.Range(Range("B4"), Cells(LastRow, LastColumn))

Also as requested, I have attached my Excel Workbook that contains the scenario that I am trying to address, plus the macros that you provided. Perhaps you can run the second one and see where I am experiencing the issue.

Hopefully, the attached Workbook will help clarify what I am trying to address. To clarify further, the following is the intent:

The attached Excel Workbook includes my 'Entry' Worksheet with the 'Tasks' Named Range defined along with the 'Dataset' Worksheet that contains two versions of the linked Named Range ('Task_Data1' and 'Task_Data2'), to illustrate that the replication process using an array or an 'Index' formula is working (that is, I can replicate the 'Tasks' Named Range from the 'Entry' Worksheet).

However, what I can't get to work is the dynamic expansion of either the 'Task_Data1' or the 'Task_Data2' Named Ranges, when I copy and paste a row in the 'Tasks' Worksheet within the green dotted lines (my Named Ranged called 'Tasks').

When I look at the 'Name Manager' information after copying and inserting a row within the green dotted lines, I see that the 'Tasks' Named Range has been extended. My real need is a way of automatically expanding the 'Task_Data1' or 'Task_Data2' Named Ranges in the 'Dataset' Worksheet.

That's where I'm hoping your macros can be of assistance. An explanation on how they can be leveraged to address this dynamic expansion of the target 'Task_Data1' or 'Task_Data2' Named Ranges would be a big help.

Cheers,

Wayne

Hi Sean,

Thanks for persevering with my need. I have now had the chance to take a good look at the code and I don't think it will work for the scenario I am trying to address.

Although it works for the example Excel file I attached, my eventual file will have multiple 'Tasks' Named Ranges. That's why I was actually naming the Range that contains my Tasks.

So what I really need is the ability to insert a whole row each time so that the whole row of information below each 'Tasks' Named Range is dynamically moved down.

The process of manually doing that is working on the 'Entry' Worksheet, but I also need to be able to replicate that behavior on the 'Dataset' Worksheet, for each of the multiple 'Task_Data' Named Ranges that replicate the 'Entry' Worksheet 'Tasks' Named Ranges.

Any suggestions on how to handle that situation would be most welcomed.

Cheers,

Wayne

Hi Sean,

Once again, thanks for coming back with additional suggestion with my situation. I too can only work on this at the end of my day.

I think the best way to clarify what I am after is to supply an updated Excel Workbook with further detail. In the attached Excel Workbook, I have added extra Tasks, as I need it in my eventual scenario. I only name each Task Range, because I can use that reference in the 'Array' and the Index formula that I can use to populate my 'Task_Data' Ranges on the second Worksheet.

If you take a look at the formulas in the 'Task_Data1' and 'Task_Data2' Named Ranges on the 'Dataset' Worksheet, you see how those two Named Ranges are being populated.

This works well when those areas do not change in size.

But what I want to be able to do is insert copied Rows on the 'Entry' Worksheet between the green dotted lines (as highlighted by the copied and pasted yellow highlighted Tasks).

What I need is for the orange highlighted Tasks to also be available on the 'Dataset' Worksheet as highlighted there.

As it currently stands, the orange rows are pushed out the bottom of the 'Task_Data3' and 'Tasks_Data4' Named Ranges.

The naming of the Ranges make the use of the array and Index formulas easier, but are not something that I really have to include, if it makes things easier without them.

Again, any other suggestions gladly accepted.

Cheers,

Wayne

Hi Sean,

If I define a name for the first cell in the range, for each range that needs to be dynamic, and use that as the starting point each time, will that work?

I will have a set number of predefined 'Tasks' Name Ranges on the 'Entry' Worksheet, so I can have the same number of starting point named cells in the 'Dataset' Workbook.

What I need to happen is to have the ability to expand or contract the ranges for each replica 'Task_Data' range in the 'Dataset' Workbook. I'm guessing that would be possible if the operations are on the full row each time?

Regarding having the data side by side rather than down the Worksheet, that is not an option for me. I am relying on a program to read my dataset and write it to a SQL Server 2008 R2 database. That program is expecting the data to be defined down the Worksheet (with Project specific information ahead of each 'Task_Data' range of information.

So my thoughts were, if I can name the top right cell in each of my 'Task_Data' ranges and then just copy and past the full set of rows from the dynamic 'Tasks' Named Range in the 'Entry' Worksheet to the starting point cell (e.g. Named 'StartTask1', StartTask2, etc...), then the inserted rows should mover everything below, but the named starting point will move as well.

This assumes that the macro can reference the Defined Name for the starting point cell, which is something I can do with formulas, but am not sure how to do so in the macro code.

Hope his makes sense.

Cheers,

Wayne

Hi Sean,

I think this may get me working in the right direction for my very specific needs. Just finished up work, but I'll be working through this on the weekend to see if I can get it working exactly how I need it to work.

I'll let you know how it turns out.

Cheers,

Wayne

Hi Sean,

Well, it's been a long time since I've done any coding, let along Excel Macro development. But after a lot of ramp up this weekend (and Google searching), I've finally pulled together the code I need to reliably replicate my Task Range to the separate Worksheet.

The Macro I created is below (along with a Function I needed for a Named Range check). I've included copious quantities of commenting throughout, mainly to help me remember what I was doing as I located solutions (search results), to the individual pieces I needed to get the overall macro working.

So my final macro code is as follows:

Sub Replicate_Tasks()
' Macro to replcate a dynamic set of Tasks from one Worksheet to another.

    ' Define variables.
    Dim i As Long ' Count of Task Ranges to process.
    Dim TR As String ' Task Range starting point.
    Dim DR As String ' Data Range starting point.
    Dim DP As String ' Data Pasted starting point.
    Dim Rng As Range ' Range for the pasted data.
    Dim RngChk As Range ' Range for existing Range check.
    
    ' Turn off screen refresh before starting the macro.
    Application.ScreenUpdating = False
    ' Start on the 'Entry' Worksheet at the top.
    Application.Goto Reference:="Start"
    ' Work through the define number of Task and Data Ranges.
    For i = 1 To 5
        ' Define the name of the Task and Data Range starting point cells.
        TR = "Task" & i
        DR = "Data" & i
        DP = "DataPasted" & i
        ' Start by going to the first Task in a Task Range to check its status.
        Application.Goto Reference:=TR
        ' Only need replicate Tasks if there is at least one Task in the Task Range being processed.
        If ActiveCell.Value <> "" Or ActiveCell.Offset(1, 0).Value <> "" Then
            ' Before replicating Tasks to the dataset, check and remove any existing records.
            If RangeExists(DP) = True Then
                Application.Goto Reference:=DP
                Range(ActiveCell, ActiveCell.End(xlDown)).Select
                Selection.EntireRow.Delete
                Application.Goto Reference:=TR
            End If
            ' If there are no existing dataset records or the dataset is cleared, just process the copy and paste.
            Range(ActiveCell, ActiveCell.End(xlDown)).Select
            Selection.EntireRow.Copy
            Application.Goto Reference:=DR
            Selection.Insert Shift:=xlDown
            ' After inserting the pasted rows, name the top left cell in the pasted range.
            Set Rng = ActiveCell
            ActiveWorkbook.Names.Add Name:=DP, RefersTo:=Rng
        End If
    Next i
    ' End on the 'Entry' Worksheet at the top, making sure the last selection is deselected.
    Application.CutCopyMode = False
    Application.Goto Reference:="Start"
    'Turn on screen refresh before ending the macro.
    Application.ScreenUpdating = True
End Sub
 
Function RangeExists(s As String) As Boolean
    On Error GoTo Nope
    RangeExists = Range(s).Count > 0
Nope:
End Function

I have also attached an updated Excel Workbook, that will demonstrate the macro running. In need this macro triggered whenever a 'Save' is made of the Workbook, so I even added it to the Before Save Event.

Thanks very much for your perseverance in helping me out with this. You got me pointed in the right direction and I think what I have ended up with will work well for what I originally envisaged the solution to be.

Many thanks!

Cheers,

Wayne