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
 

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top