In VBA for Excel, why use activecell and activesheet when copy and pasting information derived on another worksheet?

Question

I am following a YouTube tutorial on VBA for Excel. In one segment of the tutorial, the objective is to copy information from one sheet and paste it into specific cells on a different sheet within the same workbook. The correct piece of code for this process given in this part of the video is:

    Public Sub PopPandL()

Dim x As Integer
Dim sheet_title As String

For x = 1 To Worksheets.Count - 1

Worksheets(x).Select
sheet_title = ActiveSheet.Name
Sheets("P&L").Select

Selection.Offset(x * 5 + 2, 0).Select
Selection.Value = sheet_title
Worksheets(x).Select

Range("A1").Select
Selection.CurrentRegion.Copy
Sheets("P&L").Select

ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Next x

End Sub

My question concerns the activecell and activesheet components of this code. To experiment, when I try to use:

Selection.Offset(1, 0).Select
Selection.Paste

as an alternative, I get an error saying that the object does not support this property or method. Why is this?

Sadly YouTube videos aren't guaranteed to be the best... see for example [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Apr 02 '20 at 13:36
I think it's because there is no such `Paste` method for `Range` object, while there is [`PasteSpecial`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial) — HTH, Apr 02 '20 at 13:38
This code is full of things that are considered bad practices in Excel VBA. I would avoid this tutorial in the future. — RBarryYoung, Apr 02 '20 at 14:23
Sidge, do not forget to accept answers give by the community. Take the site's tour if you are unfamiliar with the mechanics. Check [here](https://stackoverflow.com/tour) — JvdV, Apr 28 '20 at 13:53

Darren Bartrup-Cook · Accepted Answer · 2020-04-03T06:56:23.220

As @Bigben said - YouTube videos aren't guaranteed. Neither are answers on here or any other forum.

As @HTH said (sorry, didn't quite understand what he was getting at until I tried it).
Paste is a Worksheet method and you're trying to use it on a range on a sheet.
ActiveSheet.Paste will paste to the selected cell on the active sheet.

Saying all that, I think this is the code you're after:

Public Sub PopPandL()
    Dim wrksht As Worksheet
    Dim PasteToRow As Long

    For Each wrksht In ThisWorkbook.Worksheets
        If wrksht.Name <> "P&L" Then
            PasteToRow = wrksht.Index * 5 + 2
            ThisWorkbook.Worksheets("P&L").Cells(PasteToRow, 1) = wrksht.Name
            wrksht.Range("A1").CurrentRegion.Copy _
                Destination:=ThisWorkbook.Worksheets("P&L").Cells(PasteToRow + 1, 1)
        End If
    Next wrksht
End Sub

On a side note I wouldn't use CurrentRegion either as that can return incorrect results - either use a method to find the last cell on the sheet containing data (if that's what you need) or maybe put your data in a table and use ListObjects.

Good point - didn't spot that. I'm guessing the last sheet is the P&L one so have updated code to miss that one. — Darren Bartrup-Cook, Apr 02 '20 at 21:27

Tsiry Rakotonirina · Answer 2 · 2020-04-02T15:08:29.567

0

This is a solution I simulated for you. As it is said in the comment, it is highly recommended not to use Selection [or Activate as it is from the comment below]:

Updated Code without Activate

'Copy from A1 of the Active Sheet
Dim mySelection As Range
Set mySelection = Range("A1")
mySelection.CurrentRegion.Copy

'Goto Sheet P&L
Sheets("P&L").Activate

'Convert the current R
'Goto the Last Line of the Sheet P&L (That's the Offsite want to do)
Dim PLLastRow As Range
Set PLLastRow = Sheets("P&L").Range(Selection.Address).Offset(1, 0)

'Paste the Clipboard on that last Sheet
PLLastRow.PasteSpecial

Old Code below: 'Copy from A1 of the Active Sheet Range("A1").Activate Selection.CurrentRegion.Copy

'Goto Sheet P&L
Sheets("P&L").Activate

'Convert the current R
'Goto the Last Line of the Sheet P&L (That's the Offsite want to do)
Dim PLLastRow As Range
Set PLLastRow = Sheets("P&L").Range(Selection.Address).Offset(1, 0)

'Activate the Cell
PLLastRow.Activate

'Paste the Clipboard on that last Sheet
PLLastRow.PasteSpecial  '<--- Use PasteSpecial as Paste doesn't exist

Hope it will help!

edited Apr 02 '20 at 15:08

answered Apr 02 '20 at 14:24

Tsiry Rakotonirina

731
4
13

2

it is highly recommended not to use `Activate`/`Active...`, too, for mostly the same reasons of "forbidding" `Select`/`Selection`. You could edit your post to reflect this – HTH Apr 02 '20 at 14:46
Actually, I prefer `Activate` because it doesn't require that the cell or the sheet receives the focus for it to work. Surely you could give me a better option then to replace `Activate`? – Tsiry Rakotonirina Apr 02 '20 at 14:53
1

Give [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read. – BigBen Apr 02 '20 at 14:55
1

of course: use direct object reference. like, for instance `Range("A1").CurrentRegion.Copy` – HTH Apr 02 '20 at 14:55
So if I understand it well, once we assign the range to a Range Variable then we do not need to Activate it anymore? – Tsiry Rakotonirina Apr 02 '20 at 15:03
Thanks @BigBen, very interesting article. Just updated my answer based on your suggestions – Tsiry Rakotonirina Apr 02 '20 at 15:09
How to activate the Sheet without `Sheets("P&L").Activate`? – Tsiry Rakotonirina Apr 02 '20 at 15:16
_"So if I understand it well, once we assign the range to a Range Variable then we do not need to Activate it anymore?"_: well it suffices referencing the object without assigning it (as my example `Range("A1").CurrentRegion.Copy`) – HTH Apr 02 '20 at 15:17
_"How to activate the Sheet without Sheets("P&L").Activate? "_ the point is you don't really need to actvate it!. – HTH Apr 02 '20 at 15:17
But what* shall we use to change the ActiveSheet to be a certain sheet of the Workbook without using `Sheetxx.Select` or `Sheetxx.Activate`? – Tsiry Rakotonirina Apr 02 '20 at 15:19
2

@TsiriniainaRakotonirina, you don't need to `Activate` a sheet unless in very specific and quite few cases, and this is not one of them. Whatever sheet child object you need to use , just use full reference up to sheet object, like, for instance `Set PLLastRow = Sheets("P&L").Range("A1")`, which references cell A1 in sheet "P&L" without any need to activate it – HTH Apr 02 '20 at 15:50
Then I am with you. I still can use it when I need to see the Sheet focused, but if it is just a background operations, then there is not need at all to activate any cell or sheet. Isn't it? – Tsiry Rakotonirina Apr 02 '20 at 15:56
@TsiriniainaRakotonirina, It is! Which reduces the activating of a sheet to a very very few cases – HTH Apr 02 '20 at 16:52
1

Just add my tuppence in here. As an example of not activating a sheet - if you're on Sheet3 the code `Sheet1.Range("A1").Copy Sheet2.Range("A2")` will still copy the contents of A1 in Sheet1 to A2 in Sheet2 (using sheet codenames for brevity). No activating or selecting needed. – Darren Bartrup-Cook Apr 03 '20 at 07:15

In VBA for Excel, why use activecell and activesheet when copy and pasting information derived on another worksheet?

2 Answers2