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

 

[Domski]

I think you need to specify a Range to paste to e.g.:

ActiveSheet.Range("A1").Paste

Dom

 

[RoryA]

Where exactly is this code located? In a ThisWorkbook module or a worksheet module or a normal module?

 

[chrislp]

DOMSKI - The range didn't make any difference, it still fell over at the same point, I had selected the range earlier in the code so it was already in the right place.....

RORYA - The code is in a module in the workbook...

Thanks guys...

 

[Legacy 68668]

You can not use Paste method to a range

ActiveSheet.Range("A1").PasteSpecial

 

[Richard Schollar]

Is the module named "Module1" or something else? Please could you indicate exactly which line the error occurs at

 

[chrislp]

SEIYA - Tried your code line, it still failed at that same point, i.e. when pasting....

RICHARDSCHOLLAR - It is "Module3" and the macro is called "CollateProgrammeReport()"

It fails at the following line...

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
    [COLOR=red][B][U]ActiveSheet.Paste
[/U][/B][/COLOR]    Application.CutCopyMode = False
    End If
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End If

 

[Richard Schollar]

Is there an actual Worksheet active when the Paste code is activated? It couldn't be a ChartSheet could it? You could avoid such a possibility by referencing the specific sheet you want to target eg:

Sheets("YourSheetNameHere").Paste

Replace the bit in red with your sheet name.

 

[chrislp]

The line..

Windows("Programme report template.xls").Activate

..is just before the paste code so I assume this is enough to activate the file.

None of the options listed have worked, it just doesn't seem to like the paste task at that point....as I mentioned, I ahve another piece of code which works and does the same thing in another file!!

 

[Richard Schollar]

What's the activecell in the destination worksheet when you paste the data? Have you tried to specifically target the cell in which to paste? Do you have any merged cells in the detsination worksheet?