Avatar of Neil Udovich
Neil Udovich
Flag for United States of America

asked on 

Rows copied but values not

I have the following code that works fine.  When I click the checkbox it copies the row over to the summary page.  Some of the cells have formulas that result in a number on the original sheet.  When the code copies however, it is just zeros.  What am I missing.  Thank you.

Sub Chbx()
Dim shp As Shape, ws As Worksheet, msg As String, c As Integer
Dim cop
c = 1
With Sheets("Summary")
cop = .Rows(1).Value
.Cells.ClearContents
.Rows(1).Value = cop
End With
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = "Summary" Then
For Each shp In ws.Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = xlCheckBox Then
                If shp.ControlFormat.Value = 1 Then
                    c = c + 1
     ' Alter "Resize" number below for the number of cells across, to copy over.
ws.Range(shp.ControlFormat.LinkedCell).Offset(, 1).Resize(, 10).Copy Sheets("Summary").Cells(c, 1)
                End If
            End If
        End If
Next shp
End If
Next ws
End Sub

Open in new window

VBAMicrosoft Excel

Avatar of undefined
Last Comment
Neil Udovich

8/22/2022 - Mon