0

I am attempting a For Each loop using the below code.

Sub child_builder()

Dim cell As Range

For Each cell In ActiveSheet.Range("D5:D102")

    If cell.Value = "Y" Then

        cell.Activate
        Selection.Offset(0, -2).Select
        Selection.Copy
        Sheets("Child").Select
        ActiveSheet.Paste
        Selection.Offset(0, 1).Select
        ThisWorkbook.Sheets("Product Builder").Activate
        Range("G6:I10").Select
        Selection.Copy
        Sheets("Child").Select
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        Selection.Offset(1, -1).Select

    End If

Next cell

End Sub

The first loop works but then fails on the next one due to the line cell.activate.

The error is

Run-time error '1004': Activate method of Range class failed.

Community
  • 1
  • 1
mbatesuk
  • 13
  • 2
  • 4
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This will very likely fix your issues. – Pᴇʜ Feb 21 '20 at 10:47
  • Does this answer your question? [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – eirikdaude Feb 21 '20 at 11:03
  • Thank you for the quick response PEH, unfortunately the article may as well be in a foreign language to me, I have very little VBA experience, I've cobbled the code I have together by watching a few YT videos and recording steps in the spreadsheet. If someone wouldn't mind clearly explaining where I have gone wrong that would be much appreciated. – mbatesuk Feb 21 '20 at 11:05
  • @mbatesuk well, I posted the link so you could learn from it. Actually you have gone wrong by using `.Select` and `Selection` a lot, which probably comes from using Macro Recorder and is because of a technical reason. But it is a very un-reliable method, and should be avoided at any cost. The article describes a technique to get rid of these `Select` statements. Recorded code always needs to be converted into some good reliable code. So it is a good idea to learn how to do it. It is something that you will always need. – Pᴇʜ Feb 21 '20 at 12:10

1 Answers1

0

Maybe this will get you started, I know its tough to start rewriting the macro recorder stuff. Consider the comments in the code.

Sub child_builder()

Dim cell As Range
Dim RowToPasteTo As Long

RowToPasteTo = 1

For Each cell In ThisWorkbook.Sheets("PutTheNameOfYourSheetHere").Range("D5:D102")

    If cell.Value = "Y" Then

    'From the cell that has a Y in it, move left 2 columns and copy that to the A1 cell on the sheet "Child". Also consider just assigning a value with = if you dont need formats etc.
    cell.Offset(0, -2).Copy ThisWorkbook.Sheets("Child").Range("A" & RowToPasteTo) 'Change the range here if you wanna paste somewhere else

    'I am not sure what you are doing now but I assume you want to copy Range G6:I10 from the "Product Builder" sheet just to the right of the value we just copy pasted?
    ThisWorkbook.Sheets("Product Builder").Range("G6:I10").Copy ThisWorkbook.Sheets("Child").Range("B" & RowToPasteTo) 'Change the range here if it needs to go somewhere else.

    'From what I can tell you want to paste stuff just below what ever you just pasted on the next loop?
    RowToPasteTo = RowToPasteTo + 5 'Move down 5 rows (because G6:I10 are 5 rows)

    End If

Next

End Sub
Czeskleba
  • 464
  • 3
  • 11
  • Works perfectly and so much more simple compared to mine, Thanks very much for your help. VBA is definitely something I'm going to get into more soon. – mbatesuk Feb 21 '20 at 11:44