copy question

[Gary Keramidas]

when you use the .copy method, is there any way to only copy formulas? or
do you have to use the paste method to achieve this?

this copies formats, too

Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")


--


Gary

[bill k]

Sheet1.Select
Range("A1:d4").Copy
Sheet2.Select
Range("e5").PasteSpecial Paste:=xlPasteFormulas

should do the trick

[Gary Keramidas]

sorry, this should be values, not formulas

when you use the .copy method, is there any way to only copy values? or
do you have to use the paste method to achieve this?

--


Gary


"Gary Keramidas" <[email protected]> wrote in message
news:%[email protected]...
> when you use the .copy method, is there any way to only copy formulas? or
> do you have to use the paste method to achieve this?
>
> this copies formats, too
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
>
> --
>
>
> Gary
>
>
>

[Access101]

Is this helpful:

Worksheets("Sheet1").Range("A1:D4").Select
Selection.Cells.SpecialCells(xlCellTypeFormulas).Copy
Destination:=Worksheets("Sheet1").Range("E5")


"Gary Keramidas" wrote:

> when you use the .copy method, is there any way to only copy formulas? or
> do you have to use the paste method to achieve this?
>
> this copies formats, too
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
>
> --
>
>
> Gary
>
>
>
>

[Jim Thomlinson]

This copies only the cells with formulas... Not sure if that is what you
want...

Sub CopyFormulas()
Dim rng As Range

On Error Resume Next
Set rng = Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "nothing was found"
Else
rng.Copy
End If
End Sub

--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

> when you use the .copy method, is there any way to only copy formulas? or
> do you have to use the paste method to achieve this?
>
> this copies formats, too
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
>
> --
>
>
> Gary
>
>
>
>

[Jim Thomlinson]

Here it is for constants...

Sub CopyFormulas()
Dim rng As Range

On Error Resume Next
Set rng = Sheet1.Range("A:A").SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "nothing was found"
Else
rng.Copy
End If
End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> This copies only the cells with formulas... Not sure if that is what you
> want...
>
> Sub CopyFormulas()
> Dim rng As Range
>
> On Error Resume Next
> Set rng = Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas)
> On Error GoTo 0
>
> If rng Is Nothing Then
> MsgBox "nothing was found"
> Else
> rng.Copy
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Gary Keramidas" wrote:
>
> > when you use the .copy method, is there any way to only copy formulas? or
> > do you have to use the paste method to achieve this?
> >
> > this copies formats, too
> >
> > Worksheets("Sheet1").Range("A1:D4").Copy _
> > destination:=Worksheets("Sheet2").Range("E5")
> >
> >
> > --
> >
> >
> > Gary
> >
> >
> >
> >

[Jake Marx]

Gary,

Gary Keramidas wrote:
> sorry, this should be values, not formulas
>
> when you use the .copy method, is there any way to only copy values? or do
> you have to use the paste method to achieve this?

You could do a copy then paste special values. Or, you could just do
something like this:

Range("B1:B10").Value = Range("A1:A10").Value

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

[Gary Keramidas]

ok, thanks everyone for the replies. i have it now

--


Gary


"Gary Keramidas" <[email protected]> wrote in message
news:%[email protected]...
> when you use the .copy method, is there any way to only copy formulas? or
> do you have to use the paste method to achieve this?
>
> this copies formats, too
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
>
> --
>
>
> Gary
>
>
>