Copy and Paste Cell Value Only - Not Formula

[aavvijit]

Hi

1. I have data from A7 to Y22 (this range is variable) in sheet 1 of file 1……​
2. In above range some of the column has hardcoded value data and some of the column has formula.​
3. Now I want to copy the above range of data (range may be variable range) and paste it to another file​
4. But while copying, it should copy the cell having only hardcoded value and formula cell should be skipped.​
5. At present I am using he following VBA, but same is working only if I execute the same in same workbook but it is not working in another workbook.​
6. At present I am moving between two workbooks with CTRL+F6 command.​

Please help in correcting the VBA

Sub Abb1()
Dim rngDestination As Range
Dim colNum As Long, i As Long

colNum = Selection.Columns.Count

Set rngDestination = Application.InputBox(Prompt:="Enter destination cell", Type:=8)

For i = 1 To colNum
If Selection.Columns(i).Range("A1").HasFormula = False Then
Selection.Columns(i).Copy Destination:=rngDestination.Offset(0, i - 1)
End If
Next i

End Sub

Thanks,
AB

 

[aavvijit]

Hi
Any help will be appreciated .......... pls. help in finding the solution in above matter.

Regards
Abhijit