Run-error 1004 -- ActiveSheet.Paste

Question

I've recorded this macro, it keeps bugging on the ActiveSheet.Paste. Can someone help me please?

thank you

Sub Macro13()
'
' Macro13 Macro
'

'
ActiveSheet.Range("$A$3:$L$10001").AutoFilter Field:=6, Criteria1:="O/S"
Columns("A:E").Select
Range("A2").Activate
Selection.Copy
ActiveSheet.Range("$A$3:$L$10001").AutoFilter Field:=6
Workbooks.Add
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Rows("1:1").Select
Selection.RowHeight = 89.25
Range("G7").Select
Windows("Test.xlsm").Activate
End Sub

ActiveSheet isn't the full object to paste into. Sheets().Range().Paste. — Cyril, Apr 09 '18 at 19:57
Hi Cyril, i edited it , it still dont work. Can you please help with more details? — Ricarmani Armani, Apr 09 '18 at 20:11
What are you trying to `Paste` exactly ? you need to have the line of `Copy` and the line of `Paste` without any lines in the middle using `AutoFilter` for instance, or `Workbooks.Add`. If you tell us better what you are trying to achieve, perhaps we can offer a more suitable and robust solution — Shai Rado, Apr 10 '18 at 04:45

score 0 · Accepted Answer · answered Apr 10 '18 at 06:16

First of all I recommend to read How to avoid using Select in Excel VBA which is absolutely necessary to follow strictly if you are a beginner. This makes your code a lot faster and cleaner.
Yes I know this is a recorded macro, and yes they always have a lot of .Select. This is why you should always re-write your recordings to get a good clean and reliable code. But recordings are good to have something to start with.

Then it is better to specify a worksheet by its name and avoid ActiveSheet where ever you can.

Also always specify a worksheet for every Range() or Cells() etc. Otherwise Excel guesses which worksheet you mean and Excel might guess something different from your guess and then fails.

So the code you tried would better look like this …

Option Explicit

Public Sub DoMyStuff()
    Dim wsSrc As Worksheet
    Set wsSrc = ActiveSheet 'better specify sheet by name: Set wsSrc = Worksheets("SheetName")

    Dim wsNew As Worksheet
    Set wsNew = Workbooks.Add().Worksheets(1) 'get first worksheet of a new added workbook

    wsSrc.Range("$A$3:$L$10001").AutoFilter Field:=6, Criteria1:="O/S"
    wsSrc.Columns("A:E").Copy wsNew.Range("A1") 'copy from source sheet directly into the new sheet

    wsNew.Cells.EntireColumn.AutoFit
    wsNew.Rows("1:1").RowHeight = 89.25
End Sub

It works now ! thank you . but i have another issue. i get this message "The picture is too large and will be truncated” when closing file. PLease help — Ricarmani Armani, Apr 10 '18 at 14:38
@RicarmaniArmani Try a `Application.CutCopyMode = False` in the end. — Pᴇʜ, Apr 10 '18 at 14:51
@RicarmaniArmani Then see here: [Excel 2013: “The picture is too large and will be truncated.”](https://superuser.com/questions/724467/excel-2013-the-picture-is-too-large-and-will-be-truncated) — Pᴇʜ, Apr 10 '18 at 15:05
@Peh, i uninstall TeamViewer and add Application.CutCopyMode = False after as you asked. It still gives me the same error message. Thank you ahead for the help. — Ricarmani Armani, Apr 12 '18 at 17:16

Run-error 1004 -- ActiveSheet.Paste

1 Answers1