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

220-221Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
I would need a sample workbook to confirm but at a guess you are copying the formula and its now pointing to the summary sheet so the formulas no longer have the right data
220-221Author Commented:
You're right.  Referencing I1, its referencing the cells on the original workbook.  Is there a way to paste the values instead of the formula thru the VBA

Sample Worksheet
regmigrantCommented:
I can't see where you are pasting the results in the code snippet you've shown but in general it would be of the format:

Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Read More
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try it like this to see if this works for you...

ws.Range(shp.ControlFormat.LinkedCell).Offset(, 1).Resize(, 10).Copy
Sheets("Summary").Cells(c, 1).PasteSpecial xlPasteValues

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
220-221Author Commented:
Perfect.  Thank you so much!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcom. Glad I could help.
regmigrantCommented:
harsh !
220-221Author Commented:
Sorry you feel that way.  I appreciate your help - I couldn't see where your code fit whereas the accepted answer was easily placed.  Chalk it up to my inability to code well and need to follow patterns.
regmigrantCommented:
Not at all, they're your points and you are right to allocate as you see fit - though acknowledging help with a split can help you get more views in the future.

But in a race with Sktneer I don't expect to come out ahead for the most part :)
220-221Author Commented:
I'll certainly remember that and appreciate the insight.  Again, I appreciate your willingness to assist.  I see you both have a great track record on here!  Moderator split points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.