After recording a copy and paste in an Excel macro, when the macro runs, it stops at the paste with-CodePudding

My workaround is to have the string already present at the bottom of all the columns before the macro runs.

The macro lines are:

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste

The error popup reads: Microsoft Visual Basic Run-time error '1004': You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size, and try pasting again.

CodePudding user response：

This should do the trick:

Application.Intersect(ActiveSheet.UsedRange, Columns("A:A")).Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
ActiveSheet.Paste

Or, this shorter version

    Application.Intersect(ActiveSheet.UsedRange, Columns("A:A")).Copy Range("B5")

CodePudding user response：

Thanks. I'll try that. My workaround was to remove the step and rely on finding one cell and using arrows to find the target cell, rather than pasting the values in multiple columns. Incidentally, the point of having a string "end of report" in the last cell of a column (and pasting it in the same row in other columns) is that I found no other way of finding and selecting the last data cell in a column. (In my use case, all the columns are the same length.) Understand, I'm building the macro with manual Excel commands, not by writing a script (because I don't know vba).