Pasting Error in VBA

[seenfresh]

I have a run time error in my macro

First off, my macro is excel generated and very basic it involves selecting and I know selecting is crazy slow! Forgive me!

Selection.Copy
Range("E6").Select
ActiveSheet.Paste
Range("D7").Select
Application.CutCopyMode = False

Can anyone help with this error and if you could offer me some advice regarding how to get rid of selecting that would be icing!

Thanks in advance,

Seen!

 

[seenfresh]

Thanks

I see two differences from the code I suggested.

First: Application.ScreenUpdating = True appears twice.
You should first say False, then True at the end. Otherwise the screen will continue to refresh the data, at a great cost in time.

Second: This line:
Range("E7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Try selecting the range first, before the paste. I don't know if this
will help, but I think it will.

Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I tried selecting the range first and the same error appeared, so I tried it the way I have shown and no difference

Thanx,

 

[Bob Rooney]

Here is your code rewritten. Please report if/where it fails

Sub AkamaiMacDaddy()
'
' Akamai Macro
' Macro recorded 9/22/2006 by WorldNow
'

'
Application.ScreenUpdating = False
With ActiveSheet
    With .Range(.Range("A7"), .Range("A7").End(xlDown))
        .Replace What:="(08/01/2006 - 08/31/2006)", _
        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    End With

    .Range("D254").Copy .Range("C7")
    .Range("D288").Copy .Range("D7")
    .Range("C7:D250").FillDown
        
    With .Range(.Range("A320"), .Range("A320").End(xlToRight))  ''' missing last cell ???
        ''''  change the header parameter from xlGuess to xlYes or xlNo
        .Sort _
            Key1:=.Range("A320"), Order1:=xlAscending, _
            Key2:=.Range("D320"), Order2:=xlAscending, _
            Key3:=.Range("F320"), Order3:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
        .Copy
    End With
    .Range("D7").PasteSpecial (xlValues)
    .Range("E7:F250").FormulaR1C1 = "=LOOKUP(2,1/((R254C[-4]:R16910C[-4]=RC1)*(R254[-1]:R16910C[-1]=RC4)),R254C[2]:R16910C[2])"
    .Range("E251:F251").FormulaR1C1 = "=SUM(R[-244]C:R[-1]C)"
    .Columns("C:D").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True

End Sub

EDIT: You may need to verify your lookup formula. In your code, it referenced row 116,910 with no column identifier.

 

[larrydunn]

I think I see the error in your original code. You are using commands in the wrong sequence:

Selection.Copy
Range("F6").Select

This should be:
Range("F6").Select
Selection.Copy

You must select a range before you can copy it for pasting. Think like a kid: grab something, then put it in your mouth. Not like an adult who says: I think I want to have quiche. Noun before verb is how it must be!

In addition, you are turning off screen updating way too late. It should be very early in the code. If you are selecting thousands of cells for copy and paste operations, it will make a major difference. If not, it's not necessary, as screen updating won't be a problem.

 

[Norie]

seenfresh

It's still not clear to me what your code is meant to do, but that's probably me.

There is one definite error I can see here.

Range("E7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

The Selection in that line of code is wrong.

There's also something wrong with the Autofills.

The code that Bob posted look's good though.

 

[seenfresh]

BOB, first thanks for your help and everyone esle!

I ran the code and still get the run time error '1004'.

"The information could not be pasted because the copy area and paste area are not the same size and shape."

We'll get it,

Thanks gentleman

 

[seenfresh]

Here is your code rewritten. Please report if/where it fails

Sub AkamaiMacDaddy()
'
' Akamai Macro
' Macro recorded 9/22/2006 by WorldNow
'

'
Application.ScreenUpdating = False
With ActiveSheet
    With .Range(.Range("A7"), .Range("A7").End(xlDown))
        .Replace What:="(08/01/2006 - 08/31/2006)", _
        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    End With

    .Range("D254").Copy .Range("C7")
    .Range("D288").Copy .Range("D7")
    .Range("C7:D250").FillDown
        
    With .Range(.Range("A320"), .Range("A320").End(xlToRight))  ''' missing last cell ???
        ''''  change the header parameter from xlGuess to xlYes or xlNo
        .Sort _
            Key1:=.Range("A320"), Order1:=xlAscending, _
            Key2:=.Range("D320"), Order2:=xlAscending, _
            Key3:=.Range("F320"), Order3:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
        .Copy
    End With
    .Range("D7").PasteSpecial (xlValues)
    .Range("E7:F250").FormulaR1C1 = "=LOOKUP(2,1/((R254C[-4]:R16910C[-4]=RC1)*(R254[-1]:R16910C[-1]=RC4)),R254C[2]:R16910C[2])"
    .Range("E251:F251").FormulaR1C1 = "=SUM(R[-244]C:R[-1]C)"
    .Columns("C:D").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True

End Sub

EDIT: You may need to verify your lookup formula. In your code, it referenced row 116,910 with no column identifier.

I have bolded the argument where I want to sort CA,D,F320:End
I'm not sure what I have to do here, it seems I cannot sort this field?

Thanx