Not able to run macro when specific Worksheet is active

Question

I have macro for copying data from one sheet GanttChart to another TableForOL. When TableForOL is active below macro can't be run:

Sub safdsfdsffds()

    copyToTable "Spare parts", "Spare parts end", "18", "GanttChart"

End Sub

I know that name is quite offensive but there must be some other reason for current problem.

Here is copyToTable sub routine:

Private Sub copyToTable(SearchWordOne As String, SearchWordTwo As String, RowToPaste As String, OperatingWorksheet As String)

    Dim FirstWord, SecondWord
            
    Set FirstWord = ThisWorkbook.Worksheets(OperatingWorksheet).Range("C:C").Find(SearchWordOne, LookIn:=xlValues)
    Set SecondWord = ThisWorkbook.Worksheets(OperatingWorksheet).Range("C:C").Find(SearchWordTwo, LookIn:=xlValues)

    ' Copy - Paste name
    With ThisWorkbook.Worksheets(OperatingWorksheet)
        .Range(Cells(FirstWord.Row + 1, FirstWord.Column), Cells(SecondWord.Row - 1, FirstWord.Column)).Copy
        ThisWorkbook.Worksheets("TableForOL").Range("B" & RowToPaste).PasteSpecial Paste:=xlPasteValues
    End With
    
    ' Copy - Paste amount
    With ThisWorkbook.Worksheets(OperatingWorksheet)
        .Range(Cells(FirstWord.Row + 1, FirstWord.Column + 10), Cells(SecondWord.Row - 1, FirstWord.Column + 10)).Copy
        ThisWorkbook.Worksheets("TableForOL").Range("C" & RowToPaste).PasteSpecial Paste:=xlPasteValues
    End With

    ' Copy - Paste price
    With ThisWorkbook.Worksheets(OperatingWorksheet)
        .Range(Cells(FirstWord.Row + 1, FirstWord.Column + 17), Cells(SecondWord.Row - 1, FirstWord.Column + 17)).Copy
        ThisWorkbook.Worksheets("TableForOL").Range("E" & RowToPaste).PasteSpecial Paste:=xlPasteValues
    End With

End Sub

Error message after running safdsfdsffds when Worksheet TableForOL is active:

pointing at first:

.Range(Cells(FirstWord.Row + 1, FirstWord.Column), Cells(SecondWord.Row - 1, FirstWord.Column)).Copy

At first glance I'd say you need to add a `.` lbefore `Cells` like that `.Range(.Cells(FirstWord.Row + 1, FirstWord.Column), .Cells(SecondWord.Row - 1, FirstWord.Column)).Copy` — Storax, Jul 18 '20 at 20:28

Vityata · Accepted Answer · 2020-07-18T20:35:51.070

Instead of:

.Range(Cells(FirstWord.Row + 1, FirstWord.Column + 10), Cells(SecondWord.Row - 1, FirstWord.Column + 10)).Copy

write exactly:

 .Range(.Cells(FirstWord.Row + 1, FirstWord.Column + 10), .Cells(SecondWord.Row - 1, FirstWord.Column + 10)).Copy

The dots before Cells make an important difference.

Without the dot before Cells the parent worksheet of the Cells is the ActiveSheet (or the worksheet in which the code is present, if it is not in a module/class). And the parent of the .Range is the one, given in the With Statement. Thus, having two different parents is causing 1004.

This is probably the most common error in vba that literally everyone hits, once they start programming here (So you can be proud of yourself, achievement unlocked).

Not able to run macro when specific Worksheet is active

1 Answers1