PDA

View Full Version : Copying range value without cellformula



Anomandaris
05-29-2009, 08:15 AM
Ive got this code to copy a set of rows into columns in the next page....but the cells i'm copying contain formula so its not showing up on the next page, I need the cell.values copied without the formula...
how do i do that?

thanks a bunch








Sub GridData()
Dim iCol As Long
Dim iOff As Long

'Application.ScreenUpdating = False

With Worksheets("art")


For iCol = 0 To 7

For iOff = 0 To 7

Worksheets("data").Cells(6, 8 * iCol + iOff + 3).Resize(3).Copy _
Worksheets("art").Cells(6 + iOff * 3, iCol + 3).Resize(3)
Next iOff


Next iCol
End With

Application.ScreenUpdating = True
End Sub

Bob Phillips
05-29-2009, 08:19 AM
Sub GridData()
Dim iCol As Long
Dim iOff As Long

'Application.ScreenUpdating = False

With Worksheets("art")


For iCol = 0 To 7

For iOff = 0 To 7

Worksheets("data").Cells(6, 8 * iCol + iOff + 3).Resize(3).Copy
Worksheets("art").Cells(6 + iOff * 3, iCol + 3).Resize(3).Paste xlPasteValues
Next iOff
Next iCol
End With

Application.ScreenUpdating = True
End Sub

Anomandaris
05-29-2009, 08:58 AM
hi xld, thanks for the code, but its giving me a 'runtime error 438'
says Object doesnt support this property or method.

clicking debug highlights the follow line of code


Worksheets("art").Cells(6 + iOff * 3, iCol + 3).Resize(3).Paste xlPasteValues

mdmackillop
05-29-2009, 09:22 AM
Set the Paste target as the Top Left cell. No need to specify the whole range.

mdmackillop
05-29-2009, 09:29 AM
It should be PasteSpecial

.Cells(6 + iOff * 3, iCol + 3).PasteSpecial xlPasteValues

Anomandaris
05-29-2009, 09:37 AM
I tried this it didnt work


Worksheets("art").Range("C6").Paste xlPasteValues

mdmackillop
05-29-2009, 09:50 AM
See post #5