Rows copied but values not

220-221
220-221 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

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
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

Commented:
Perfect.  Thank you so much!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcom. Glad I could help.

Author

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.
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 :)

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial