PasteSPecial Formulas and Formates

[gheyman]

Knowing that if I wanted to Paste Formulas and Formats that it’s the equivalent to a simple paste, but that doesn’t see to work if I try to modify what I am doing with this code.

I changed to .Cells(101, e).Paste but it doesn’t like this part of the code.

How do I change the following to paste all? Or do I need to run through this and Pasteformat and then PasteFormulas?

Dim e As Long
Dim LastCol As Long

With Sheets("Cash Flow")
LastCol = .Cells(1, .Columns.Count).End(xlUp).Column
End With

With Sheets("Cash Flow")
.Range("D101:D106").Copy
For e = 5 To LastCol
If Len(.Cells(6, e).Value) <> 0 Then
.Cells(101, e).PasteSpecial Paste:=xlPasteFormulas
End If
Next e
End With

 

[Andrew Poulsom]

The Range object doesn't have a Paste method. You could use xlPasteAll as the Paste argument for the PasteSpecial method. Or you could use the Destination argument of the Copy method.

 

[p45cal]

Does this really work?:
LastCol = .Cells(1, .Columns.Count).End(xlUp).Column

I'd have expected xlToLeft

Sub blah()
Dim e As Long
Dim LastCol As Long

With Sheets("Cash Flow")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For e = 5 To LastCol
        If Len(.Cells(6, e).Value) <> 0 Then
            .Range("D101:D106").Copy .Cells(101, e).Resize(6)
        End If
    Next e
End With
End Sub

 

[VoG]

Try

If Len(.Cells(6, e).Value) <> 0 Then
    .Cells(101, e).PasteSpecial Paste:=xlPasteFormulas
    .Cells(101, e).PasteSpecial Paste:=xlPasteFormats
End If

 

[markmzz]

Try this:

With Sheets("Cash Flow")
    .Range("D101:D106").Copy
    For e = 5 To LastCol
        If Len(.Cells(6, e).Value) <> 0 Then
            [COLOR=blue][B].Paste .Cells(101, e)[/B][/COLOR]
        End If
    Next e
End With

Markmzz

 

[gheyman]

All suggestions worked. Thank You