VBA Sub working only when Sheet 3 is active

[Rosto]

Hello to all

As the title says, I made a sub for anactiveX button, witch is in Sheet1, it works perfectly, but only whenSheet3 is active and I run it from VBA window.
If I try to run it from Sheet1, wherethe button is: "Run-time error 1004. Select method classfailed."
I'm newbie in VBA and coding ingeneral, I use only the mathematics and logic that I have.
I have to copy some cells from Sheet1(in a kind of range) in sheet3.
Sheet1 has a lot of merged cells and itcan not be modified and that's why offset is so weird.
Please tell me what is wrong.
Here is the code, red text is where debug send me:

Private Sub Transfer_Click()

Dim x As Integer
x = 11

While Cells(x, 1) <> ""

[COLOR=#ff0000]Sheets("Sheet3").Range("A1").Select[/COLOR]
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell =Sheets("Sheet1").Range("L65").Value
ActiveCell.Offset(0, 1) =Year(Sheets("Sheet1").Range("Q8"))
ActiveCell.Offset(0, 2) =Month(Sheets("Sheet1").Range("Q8"))
ActiveCell.Offset(0, 3) =Day(Sheets("Sheet1").Range("Q8"))
ActiveCell.Offset(0, 4) =Sheets("Sheet1").Cells(x, 1).Value
ActiveCell.Offset(0, 5) =Sheets("Sheet1").Cells(x, 1).Offset(0, 1).Value
ActiveCell.Offset(0, 6) =Sheets("Sheet1").Cells(x, 1).Offset(0, 18).Value
ActiveCell.Offset(0, 7) =Sheets("Sheet1").Cells(x, 1).Offset(0, 20).Value
ActiveCell.Offset(0, 8) =Sheets("Sheet1").Cells(x, 1).Offset(0, 24).Value
ActiveCell.Offset(0, 9) =Sheets("Sheet1").Cells(x, 1).Offset(0, 29).Value

x = x + 1

Wend

End Sub

Thanks for all your answers.
Rosto
Windows10, Excel2016

 

[wideboydixon]

You can't select a cell that's not on the active sheet. You can put a line in above the red line:

Sheets("Sheet3").Select

Having said that, the code might be inefficient. If you're looking for the next available row in Sheet3, you might be able to do this:

Private Sub Transfer_Click()

Dim thisRow As Long
Dim nextRow As Long

thisRow = 11
If IsEmpty(Sheets("Sheet3").Range("A1")) Then
    nextRow = 1
Else
    nextRow = Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "A").End(xlUp).Row + 1
End If

While Sheets("Sheet1").Cells(thisRow, "A").Value <> ""

    With Sheets("Sheet3")
        .Cells(nextRow, "A") = Sheets("Sheet1").Range("L65").Value
        .Cells(nextRow, "B") = Year(Sheets("Sheet1").Range("Q8").Value)
        .Cells(nextRow, "C") = Month(Sheets("Sheet1").Range("Q8").Value)
        .Cells(nextRow, "D") = Day(Sheets("Sheet1").Range("Q8").Value)
        .Cells(nextRow, "E") = Sheets("Sheet1").Cells(thisRow, "A").Value
        .Cells(nextRow, "F") = Sheets("Sheet1").Cells(thisRow, "B").Value
        .Cells(nextRow, "G") = Sheets("Sheet1").Cells(thisRow, "S").Value
        .Cells(nextRow, "H") = Sheets("Sheet1").Cells(thisRow, "U").Value
        .Cells(nextRow, "I") = Sheets("Sheet1").Cells(thisRow, "Y").Value
        .Cells(nextRow, "J") = Sheets("Sheet1").Cells(thisRow, "AD").Value
    End With
    
    thisRow = thisRow + 1
    nextRow = nextRow + 1

Wend

End Sub

WBD

 

[Rosto]

Thanks a lot.
I swear i tried it, but maybe i miss-typed something.

Thanks again