troubleshooting Question

Rows copied but values not

Avatar of 220-221
220-221 asked on
VBAMicrosoft Excel
10 Comments1 Solution65 ViewsLast Modified:
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
.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

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 10 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 10 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004