To ask for the destination sheet.

Sub copynonformulas()
Dim mysheet As String
Dim c As Range
mysheet = InputBox("which sheet")
For Each c In Range("c7:c11")
If Not c.HasFormula Then
Sheets(mysheet).Range(c.Address).Value = c.Value
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Adapt this simplified version to your needs.

Option Explicit
Sub copynonformulas()
Dim c As Range
For Each c In Range("c7:c11")
If Not c.HasFormula Then
Sheets("sheet2").Range(c.Address).Value = c.Value
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Leo" wrote in message
...
Dear Experts,
I have a worksheet with full of formulas, whose data parts need to be
filled
by other users , and then I have to transfer this data to my worksheet.
I created the below Sub to copy all data , except formula cells, to my
original sheet at once, without selecting data parts and copy them one by
one.
'====
Sub CopyValues()
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim TargetRange As Range
Dim c As Range
Dim stAddress As String
'target sheet is selected by user
Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!",
Title:="Target?", Type:=8)
Set TargetSheet = TargetRange.Parent

For Each c In SourceSheet.Range("A1:X2000")
If Not c.HasFormula Then
stAddress = c.Address
TargetSheet.Range(stAddress).Value = c.Value
End If
Next c


End Sub
'====
on the line of setting TargetSheet, I encountered error # 91, which I do
not
know the reason.
Could you please tell me what is wrong, and how to correct???
--
Thans & Best regards
Leo, InfoSeeker