VBA error 1004 Selecting range of worksheet fails

Question

I want to copy a part of my sheet to another sheet when in column "M" an "X" has been written:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim triggercells    As Range
Dim lastrow         As Long
    
Set triggercells = Range("M1:M100")

If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then
           
    If Target.Value = "X" Or Target.Value = "x" Then
        ThisWorkbook.Worksheets("Offen").Range(Cells(Target.Row, 1), Cells(Target.Row, 13)).Select ' select part of changed row
        Selection.Copy
        
        ThisWorkbook.Worksheets("Erledigt").Visible = True 'unhide sheet
        ThisWorkbook.Worksheets("Erledigt").Select

            lastrow = (ThisWorkbook.Sheets("Erledigt").Cells(Rows.Count, 1).End(xlUp).Row) + 1 'get last emty row

but throws me out here:

            ThisWorkbook.Worksheets("Erledigt").Range(Cells(lastrow, 1), Cells(lastrow, 13)).Select ' select part of row to paste to
                Selection.Paste
                
        ThisWorkbook.Worksheets("Offen").Select 'select sheet
        ThisWorkbook.Worksheets("Erledigt").Visible = False 'hide sheet
        Application.CutCopyMode = False
        
    End If
    
End If

End Sub

with said runtime error.

score 1 · Accepted Answer · answered Jan 05 '21 at 12:07

1

The error is because Cells(lastrow, 1) does not take as parent the ActiveSheet "Erledigt", but the worksheet, in which the event code resides. So the Range and the Cells have different parents... To have the same parent worksheet, it should be indicated explicitly, by rewriting this part: Range(Cells(lastrow, 1), Cells(lastrow, 13)).Select

to this:

With ThisWorkbook.Worksheets("Erledigt")
    .Range(.Cells(lastrow, 1), .Cells(lastrow, 13)).Select
End With

In general, it is a good idea to How to avoid using Select in Excel VBA, but this is the next level.

answered Jan 05 '21 at 12:07

Vityata

42,633
8
55
100

I try to clean up my code when i am done and delete selects where i can, because of reasons of performance.. I am somehwat comfortable with programming in general (I am getting where i want to get to usually, like some Minecraft mod i've written in the past), but I feel like Excel's VBA is a bit "less straight forward" then java.(though not hard) – Leon S Jan 05 '21 at 13:20
Oh also, do you need to "unhide" sheets to work with the contained cells? – Leon S Jan 05 '21 at 13:21
1

@LeonS - VBA is really a bit of its own kingdom, not fair to compare it with java. And for the second question - the answer is "no", but the good thing is that you could have tested it for less time than writing the comment. Keep on trying testing stuff, the macro recorder is something that Java does not have :) – Vityata Jan 05 '21 at 13:25
1

Well i found this while researching if you can or not: https://excelribbon.tips.net/T009329_Running_Macros_on_Hidden_Worksheets.html – Leon S Jan 05 '21 at 13:30

VBA error 1004 Selecting range of worksheet fails

1 Answers1