CHANGING THE REFERENCE OF THE SHAPES WHEN THE SHAPES ARE DUPICATED

GopiBalaraju 

To achieve dynamic referencing of shapes when duplicating them in Office 365 using VBA macros, you can adjust the properties of the newly created shapes based on their positions. This approach allows the duplicated shapes to reference different cell ranges as needed.

Here’s a step-by-step guide on how to do this:

1. Create the Macro to Copy and Paste Shapes:

This macro will copy the shapes from one range, paste them, and then adjust their properties to reference a new cell range based on their positions.

2. Adjust the Cell References Dynamically:

After copying and pasting the shapes, you will need to calculate the new cell range and update the shapes' references accordingly.

Here's an example VBA code snippet to illustrate this process:

Vba Code is untested backup your file first.

Sub DuplicateShapesAndChangeReference()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    Dim originalShape As Shape
    Dim newShape As Shape
    Dim originalRange As Range
    Dim newRange As Range
    
    ' Define the original range
    Set originalRange = ws.Range("A1:E33")
    
    ' Copy and paste the shapes from the original range
    For Each originalShape In ws.Shapes
        If Not Intersect(originalShape.TopLeftCell, originalRange) Is Nothing Then
            originalShape.Copy
            ws.Paste Destination:=ws.Cells(originalShape.TopLeftCell.Row, originalShape.TopLeftCell.Column + 6)
        End If
    Next originalShape
    
    ' Adjust the references of the newly pasted shapes
    For Each newShape In ws.Shapes
        If Not Intersect(newShape.TopLeftCell, ws.Range("G1:G33")) Is Nothing Then
            ' Change the reference of the shape as per the new position
            ' Assuming you're adjusting a cell reference in the shape text
            ' Example: change "A1:E33" to "G1:G33"
            
            ' You may need to adjust the logic depending on what the shapes reference
            newShape.TextFrame2.TextRange.Text = Replace(newShape.TextFrame2.TextRange.Text, "A1:E33", "G1:G33")
        End If
    Next newShape
End Sub

Explanation:

1. Set the Worksheet:
• Assign the worksheet where the shapes are located.
2. Define the Original Range:
• Specify the range of cells that the original shapes reference.
3. Copy and Paste Shapes:
• Loop through each shape and check if it intersects with the original range.
• Copy the shape and paste it to a new location (adjusted by column offset, in this case, 6 columns to the right).
4. Adjust New Shape References:
• Loop through each new shape and adjust its reference based on its new position.
• In this example, the text within the shapes is modified to reflect the new cell range.

Important Notes:

• Ensure that the logic for adjusting the shape references matches your specific requirements. The above example assumes that the shapes contain text referencing cell ranges and that you need to adjust this text.
• Modify the column offset (in the Destination argument of the Paste method) as per your needs.
• You might need to add additional checks or modify the Replace function based on how the shapes are originally referencing the cell ranges.

This approach should help you dynamically update the references of the duplicated shapes based on their new positions in the worksheet. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.