How to do get rid of activesheet in the code?

Question

I know that it is bad practise to use activesheet as reference in the code but I'm not able to get rid of it without breaking the code.

Here is my code:

For Each HeaderMatch In SrchRange
    If HeaderMatch Like HeaderKeyWord Then    
        HeaderCount = HeaderCount + 1
        HeaderRows(HeaderCount) = HeaderMatch.Row
        Sheets("Data").Cells(HeaderMatch.Row, 1).Resize(3, 7).Copy
        Sheets("Critical Components").Select
        NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 3
        ActiveSheet.Cells(NextRow, 1).Select
        ActiveSheet.Paste        
    End If
Next

My code is running in the button which is located at the "Main" sheet. There is Data on the "Data" sheet where I collect all the 3 row headers which contain HeaderKeyWord on their first row. Those 3 rows are then copien from "Data" to "Critical Components" sheet.

This code is basically copied from the sample found here. The code heavily relies on ActiveSheet and Select which I guess is not good practice and I have been struggling with these as it often seems quite misleading.

Current code works and will do what it supposed to but I would like to make it with absolute referencies like Sheets("Data") etc..

.Select and .Paste just won't work if ActiveSheet is replaced with Sheets("Critical Components").

I don't get it why those methods(?) applies for ActiveSheet but not for sheets specified with Sheets?

Thanks for all of you about correct code examples. It seems to be that I better forget that copy paste method as there is something strange about .Select and .Paste. For sake of learning Can someone explain how ActiveSheet. and Sheets(). are so different they can't be interchangeable for .Select and .Paste? For .Copy it could be done.. — JonasJ, Nov 23 '17 at 15:13

score 0 · Answer 1 · answered Nov 23 '17 at 14:38

Give it a try like this:

Sub TestMe()
    For Each HeaderMatch In SrchRange
        If HeaderMatch Like HeaderKeyWord Then

            HeaderCount = HeaderCount + 1
            HeaderRows(HeaderCount) = HeaderMatch.Row
            NextRow = Worksheets("WriteTheName").Cells(Rows.Count, 1).End(xlUp).Row + 3

            Sheets("Data").Cells(HeaderMatch.Row, 1).Resize(3, 7).Copy _
                            Destination:=Sheets("Critical Components").Cells(NextRow, 1)

        End If
    Next
End Sub

Check out some ideas here: https://stackoverflow.com/a/35864330/5448626

Furthermore, make sure that you always refer to the worksheet in your code, whenever you use Cells() and Range().

Thanks, Seems to work. I guessed that copy-paste should be replaced some other method. I just don't understand why that selecting and pasteing are so problematic.. — JonasJ, Nov 23 '17 at 14:49
@JonasJ - it is problematic, because you may lose control of the results easily. — Vityata, Nov 23 '17 at 14:50

h2so4 · Answer 2 · 2017-11-23T18:00:09.467

you can replace activesheet by the name of the sheet you want to consider so your code could look like this

For Each HeaderMatch In SrchRange
    If HeaderMatch Like HeaderKeyWord Then
        HeaderCount = HeaderCount + 1
        HeaderRows(HeaderCount) = HeaderMatch.Row
        NextRow = Sheets("Critical Components").Cells(Rows.Count, 1).End(xlUp).Row + 3
        Sheets("Data").Cells(HeaderMatch.Row, 1).Resize(3, 7).Copy Sheets("Critical Components").Cells(NextRow, 1)
    End If
Next

score 0 · Answer 3 · answered Nov 23 '17 at 14:46

I was trying to make the minimum modification to your current code, try the version below (explanations inside the code's comments):

For Each HeaderMatch In SrchRange
    If HeaderMatch Like HeaderKeyWord Then
        HeaderCount = HeaderCount + 1
        HeaderRows(HeaderCount) = HeaderMatch.Row

        With Sheets("Critical Components")
            ' first: get the next row from sheets "Data"
            NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 3

            ' second: copy>>paste is a 1-line command
            Sheets("Data").Cells(HeaderMatch.Row, 1).Resize(3, 7).Copy Destination:=.Cells(NextRow, 1)
        End With        
    End If
Next HeaderMatch

How to do get rid of activesheet in the code?

3 Answers3