make an iteration of very simple action on every sheet

Question

I am not quite good at VBA programming. I need VBA to make an iteration of very simple action on every sheet; I have written the code to obtain this iteration, however whenever I run it, it says:

"Compile Error: Method or data member not found"

and it highlights the first line: Sub Macro1 ()

What did I make wrong?

Sub Macro1()
Dim ws As Worksheet

    For Each ws In Worksheets
        With ws
            .Activate
            .Application.Goto Reference:="R1C1"
            .Application.Goto Reference:="R10C3"
            .Range(Selection, Selection.End(xlDown)).Select
            .Range(Selection, Selection.End(xlToRight)).Select
            .Selection.Copy
            .Application.Goto Reference:="R10C12"
            .ActiveSheet.Paste
        End With
    Next ws
End Sub

A worksheet does not have a `Selection` property/method, and it also does not have a property called `ActiveSheet`. — braX, Apr 25 '23 at 08:09
Maybe it would help to explain what it is that you are trying to do to each sheet, as `Activate` and `Goto` and `Select` and `Copy` and `Paste` are all things that should be avoided. — braX, Apr 25 '23 at 08:11
I have the impression that you want to copy the range "A1:C10" and copy this to the range "L10:N19", am I correct? If so, you might have a look at "https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba". — Dominique, Apr 26 '23 at 06:32

score 0 · Answer 1 · answered Apr 26 '23 at 05:49

try

Sub Macro1()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    
    For Each ws In Worksheets
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).row
        lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        ws.Range(ws.Cells(10, 3), ws.Cells(lastRow, lastCol)).Copy ws.Cells(10, 12)
    Next ws
End Sub

make an iteration of very simple action on every sheet

1 Answers1