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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,688
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,099,683
Messages
5,470,108
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top