ActiveSheet.Paste error

[chrislp]

Hi there, I have looked at previous posts and found some information about ActiveSheet.Paste errors, which appear to be related to having activate, select and paste functions in the wrong order.

I am still getting an error at the ActiveSheet.Paste point of my code which I cannot seem to overcome, although this same code works on another macro doing exactly the same action...

ChDir _
        "[URL="file://workgroup/Highlight_reporting/Templates"]\\workgroup\Highlight_reporting\Templates[/URL]"
    Workbooks.Open Filename:= _
        "[URL="file://workgroup/Highlight_reporting/Templates/Programme"]\\workgroup\Highlight_reporting\Templates\Programme[/URL] report template.xls"
    Windows("Milestones_matrix.xls").Activate
    Application.Run "'Milestones_matrix.xls'!ResetFilters"
    Range("A11").Select
    Sheets("All Milestones").Select
    Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
    LR = Cells(65536, 3).End(xlUp).Row
    If LR > 2 Then
    Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
    Windows("Programme report template.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End If
    Range("A49").Select
    Windows("Milestones_matrix.xls").Activate
    Application.Run "'Milestones_matrix.xls'!ResetFilters"
    Selection.AutoFilter Field:=19, Criteria1:="=1", Operator:=xlAnd
    LR = Cells(65536, 3).End(xlUp).Row
    If LR > 2 Then
    Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
    Windows("Programme report template.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End If
    End Sub

Any ideas why this fails yet works on another similar macro in the right way?
Cheers

 

[chrislp]

The active cell to begin the paste function is A11, there are no merged cells in the range...

 

[chrislp]

Managed to fix it using the code from another macro which was working. If you are having problems pasting data, especially filtered data, and you keep getting paste errors, you might be able to use some of the code below...

Workbooks.Open Filename:= _
        "[URL="file://dfs52609/100400001/workgroup/Pensions-ERSP-PMO/PMO/5.Highlight_reporting"]Highlight_reporting[/URL] dashboards\Templates\Programme report template.xls"
    Range("A11").Select
    Windows("Period 2_Milestones_matrix.xls").Activate
    Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
    LR = Cells(65536, 3).End(xlUp).Row
    If LR > 2 Then
    Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
    Windows("Programme report template.xls").Activate
    Range("A11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A19").Select
    End If
    Windows("Period 2_Milestones_matrix.xls").Activate
    Application.Run "'Period 2_Milestones_matrix.xls'!ResetFilters"
    Selection.AutoFilter Field:=19, Criteria1:="=1", Operator:=xlAnd
    LR = Cells(65536, 3).End(xlUp).Row
    If LR > 2 Then
    Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
    Windows("Programme report template.xls").Activate
    Range("A49").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M49").Select
    End If

Thanks for the assistance guys....

 

[Prolificscarcity]

I had the same issue. I don't know much about macros. However, my macro worked until one day it didn't. and it was an ActiveSheet.Paste error. I was simultaneously noticing another paste related error, which could not have been a coincidence. The other error was that when I selected and dragged cells around, It would show me an error saying [h=3]'Cannot empty the Clipboard'.[/h]I was able to fix this by going to Excel Options -> Advanced -> UNCHECK 'Show Paste Options button'
I restarted excel/comuter and ran the macro again..... and behold....it worked!

Hi there, I have looked at previous posts and found some information about ActiveSheet.Paste errors, which appear to be related to having activate, select and paste functions in the wrong order.

I am still getting an error at the ActiveSheet.Paste point of my code which I cannot seem to overcome, although this same code works on another macro doing exactly the same action...

Any ideas why this fails yet works on another similar macro in the right way?
Cheers

 

[DocRyan66]

I recently had similar issue in that my VBA was working fine, then after other users had worked on the spreadsheet the 'Add Line' part of my code kept crashing with "Run-time error '104': That comand cannot be used on multiple selections" it took me a short while to figure they'd applied a filter to the sheet, soon as I cleared this all worked again.

 

[BadgerBot]

Hi Guys, this is just for anyone else searching this problem. I got exactly the same issue and it was related to protected sheets. You need to unprotect the destination sheet before the select copy command. This solved the problem for me. I'm a newbie so apologies if this is grannies and eggs.