Restore Excel VBA Shapes to Their Original Positions on Mac
Question details
Restore worksheet shapes to their original positions and preserve those positions for future use.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- macOS
- Scenario
- Shapes on an Excel worksheet have moved, and the user wants a repeatable way to return them to their initial locations.
- Observed behavior
- Shapes no longer appear in their original worksheet positions.
Check whether the original positions were saved before the shapes moved; otherwise, look for an earlier workbook version. To use VBA, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only if you trust the code.
Save and restore shape coordinates with VBA
Use this method to capture shape positions before they move and restore them later on the same worksheet.
The macros save each shape's name and its Left and Top coordinates in points. They store one worksheet's snapshot at a time, so saving another snapshot replaces the previous one.
The macros cannot reconstruct positions that were never saved. They also skip shapes that have been deleted or renamed since the snapshot.
In the workbook, add a blank worksheet named _ShapePositions. Use this sheet only for the snapshot: the save macro clears all of its cell contents.
Open Tools > Macro > Visual Basic Editor, choose Insert > Module, and paste this code: Sub SaveShapePositions() Dim ws As Worksheet, store As Worksheet Dim shp As Shape, r As Long Set ws = ActiveSheet Set store = ThisWorkbook.Worksheets("_ShapePositions") If ws.Name = store.Name Then MsgBox "Select the worksheet with the shapes first." Exit Sub End If store.Cells.ClearContents store.Range("A1").Value = ws.Name store.Range("A2").Value = "Shape name" store.Range("B2").Value = "Left" store.Range("C2").Value = "Top" r = 3 For Each shp In ws.Shapes store.Cells(r, 1).Value = shp.Name store.Cells(r, 2).Value = shp.Left store.Cells(r, 3).Value = shp.Top r = r + 1 Next shp MsgBox "Shape positions saved." End Sub Sub RestoreShapePositions() Dim ws As Worksheet, store As Worksheet Dim shp As Shape, r As Long, lastRow As Long Set ws = ActiveSheet Set store = ThisWorkbook.Worksheets("_ShapePositions") If ws.Name <> CStr(store.Range("A1").Value) Then MsgBox "Select the worksheet used for the saved snapshot." Exit Sub End If lastRow = store.Cells(store.Rows.Count, 1).End(xlUp).Row For r = 3 To lastRow Set shp = Nothing On Error Resume Next Set shp = ws.Shapes(CStr(store.Cells(r, 1).Value)) On Error GoTo 0 If Not shp Is Nothing Then shp.Left = store.Cells(r, 2).Value shp.Top = store.Cells(r, 3).Value End If Next r MsgBox "Available shapes restored." End Sub
Select the worksheet containing the shapes while they are still in the desired positions. Choose Tools > Macro > Macros, run SaveShapePositions, and save the workbook.
Select the same worksheet, open Tools > Macro > Macros, and run RestoreShapePositions. Save the workbook after checking the layout.

Recover an earlier shape layout from version history
Use an earlier workbook version if the shapes have already moved and no position snapshot exists.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easier to move everyday spreadsheet work between applications. It does not fix Excel for Mac's VBA behavior; macro support and compatibility can vary.

Frequently Asked Questions
Can I restore shape positions if I never saved them?
The VBA method needs a snapshot made before the shapes moved. If none exists, check Version History for an earlier workbook or use another record of the original layout.
What do the Left and Top properties measure?
They record a shape's position in points relative to the worksheet. The restore macro assigns the saved values back to each matching shape.
What happens if I rename or delete a shape?
The restore macro matches shapes by name, so it skips a deleted shape or one whose name has changed since the snapshot.
Can I save positions for several worksheets with this code?
The example stores one worksheet snapshot at a time in _ShapePositions. Running SaveShapePositions again replaces the previous snapshot.
Why might restored shapes not line up with the same cells?
The macro restores absolute Left and Top coordinates, not cell references. If row heights or column widths changed after the snapshot, the shapes may no longer align with the same cells.




