Code works however, I need it to only bring in the values, not the formula

quintin

New Member
Joined
Jun 26, 2013
Messages
48
I have vba code that matches up to a list of worksheets in a column, in column B i need the code (which I have, however just want the values) to relate to "D1". Please see the code I have already. Many Thnx
Code:
Private Sub CommandButton3_Click()

For Each wkSht In Sheets

For Each cell In Sheets("SHEETSNAMES").Range("A1:A200")

If cell.Value = wkSht.Name Then

wkSht.Range("D1").Copy Destination:=cell.Offset(0, 1)

Application.CutCopyMode = False

End If

Next cell

Next wkSht

End Sub[code]
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,886
Office Version
365, 2010
Platform
Windows, Mobile
Maybe..
VBA Code:
Private Sub CommandButton3_Click()

    For Each wkSht In Sheets

        For Each cell In Sheets("SHEETSNAMES").Range("A1:A200")

            If cell.Value = wkSht.Name Then

                'wkSht.Range("D1").Copy Destination:=cell.Offset(0, 1)
                
                cell.Offset(0, 1).Value = wkSht.Range("D1").Value
                
                'Application.CutCopyMode = False

            End If

        Next cell

    Next wkSht

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,388
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top