VBA code for copying data as values not formu

Microsoft Office excel 2007 home & stude...
July 22, 2010 at 05:59:05
Specs: Windows XP, 2GB
The code copies a value from a sheet "Main Form" and pastes it into the "Industrywise". But it shows (=+#REF!/C6) error. the codes are as follows:
Dim rw As Long, v As Variant
Dim i As Long, cell As Range
Dim bfound As Boolean

rw = 6

For Each cell In Worksheets("Main Form").Range("B6:B505")
bfound = False
For i = LBound(v) To UBound(v)
If LCase(cell) = LCase(v(i)) Then
bfound = True
Exit For
End If
If bfound Then
cell.Copy Worksheets("Industrywise").Cells(rw, "B")
cell.Parent.Cells(cell.Row, "H").Copy Worksheets("Industrywise").Cells(rw, "D")

rw = rw + 1
End If

please bring necessary modification so that it copies column 'H' of "Main Form" as just values only to 'Column D' of 'Industrywise' sheet.

thank you.

See More: VBA code for copying data as values not formu

Report •

July 22, 2010 at 10:58:14

Use PasteSpecial.
The copy and paste must be two separate functions like this:

Range("C21").PasteSpecial xlPasteValues

There are other options, such as xlPasteValuesAndNumberFormats

The reason for your #REF error is likely that you copied a cell that contains a formula and the code pasted the formula. In its new location, the formula reference was no longer valid.
For example coying a formula in row 20 that contains =A1/6
and pasting it to row 6 on the destination sheet, will attempt to create a reference to a cell that is 19 rows above it. But as there are only 5 rows above the destination cell, the reference is replaced by an error message, but the rest of the formula is retained.

If you paste just the value from the source cell to a destination cell, you will not get this error.


Report •
Related Solutions

Ask Question