Auto Shapes not refreshing when running macro

Yuffster

New Member
Joined
Feb 3, 2015
Messages
7
I have a macro to copy and paste information from one sheet into another, looped to cycle through the different drop down options at the top of the page (i.e. you end up with one separate tab for each drop down option)

One part of the sheet contains text boxes linked to specific cell values, which copy across absolutely fine if I do this manually, but if I run the macro, all the text boxes show as 0 value, even though the formula is still set and the cells they are linked to still have a value in them. The only way to update the values in the text boxes seems to be to manually enter each text box twice! I have tried a few different 'refresh all objects' functions but none seem to do the trick. It is all very puzzling and I wondered if anyone had encountered a similar problem or can spot where I may have gone wrong please?


Here is the copy and paste macro:

Sub CopySheets()
'

Dim masterSheet As Worksheet
Dim hiddenSheet As Worksheet
Dim NewSheet As Worksheet
Dim myBook As Workbook
Dim lastRow As Long
Dim i As Long
Dim namesColumn
Dim Rng As String
On Error Resume Next

Application.ScreenUpdating = False

Set myBook = ActiveWorkbook
Set masterSheet = myBook.Worksheets("Mapping")
Set hiddenSheet = myBook.Worksheets("BSegment")
namesColumn = 6
lastRow = masterSheet.Cells(masterSheet.Rows.Count,namesColumn).End(xlUp).Row

For i = 1 To lastRow
With myBook
'New sheet
Set NewSheet =.Worksheets.Add(After:=.Worksheets("BSegment"))
End With

tabName =masterSheet.Cells(i, namesColumn)
NewSheet.Name =tabName

Rng =masterSheet.Cells(i, namesColumn)
hiddenSheet.Select
Range("A2").Value = Rng

Dim Ctrl As Object

For Each Ctrl InActiveSheet.OLEObjects
IfTypeName(Ctrl.Object) = "ComboBox" Then
Ctrl.Object.Text = Range("A2").Value
End If
Next

hiddenSheet.Select
Cells.Select
Selection.Copy
NewSheet.Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _
:=False,Transpose:=False

NewSheet.Cells(1,1).Value = tabName
Next i


End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top