Copy certain columns code dependant on outcome of Loop

[Amateurhour101]

Hi all, I am trying to copy Columns A to D where the Column 5 ("E") is Inventory, to another tab -> aptly called Inventory. Excel keeps having trouble with the yellow line in the script below. I would appreciate any help to this issue.

Many thanks
Dave

Sub Inventory_Loop()

Dim irow As Long



A = Worksheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To A

If Worksheets("DATA").Cells(i, 5).Value = "INVENTORY" Then

Worksheets("DATA").Range(Cells(irow, "A"), Cells(irow, "D")).Copy

Worksheets("Inventory").Activate

Worksheets("Inventory").Cells(1,3).Select

ActiveSheet.Paste

Worksheets("DATA").Activate

End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("DATA").Cells(1, 1).Select

End Sub

 

[Joe4]

Welcome to the Board!

It looks like you are using "irow" in that formula, but you haven't set "irow" to anything yet. So it is trying to access row 0, which is not possible.
Did you, perhaps, mean to use "i" instead of "irow"?

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

With Worksheets("DATA")
     .Range(.Cells(i, "A"), .Cells(i, "D")).Copy
End With

 

[Amateurhour101]

Hi thanks for your comments I am still encountering an issue with the same line. Apologies for not putting the text as easy to read. It seems whatever i try to put in that line after the if, excel doesn't like or want to accept? Many thanks in advance.

Sub Inventory_Loop()

A = Worksheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To A

    If Worksheets("DATA").Cells(i, 5).Value = "INVENTORY" Then
    
With Worksheets("DATA").Range(Cells(i, "A"), Cells(i, "D")).Copy

End With

        Worksheets("Inventory").Activate

        Worksheets("Inventory").Cells(1, 3).Select

        ActiveSheet.Paste

        Worksheets("DATA").Activate

    End If

 

[Fluff]

You need to write that line like I showed in post#3 otherwise it will only work if the Data sheet is the activesheet.

 

[Joe4]

It looks like you combined a few rows together, combining your WITH statement with you COPY command.
Get rid of the "WITH" clause (you aren't doing anything with it by changing this:

With Worksheets("DATA").Range(Cells(i, "A"), Cells(i, "D")).Copy

End With

to this:

Worksheets("DATA").Range(Cells(i, "A"), Cells(i, "D")).Copy

 

[Fluff]

@Joe4 that will only work if the data sheet is active, as the cells portion has not been qualified with the sheet name.

 

[Joe4]

that will only work if the data sheet is active, as the cells portion has not been qualified with the sheet name.

Good point. I was just pointing out the error in that line of code.

 

[Amateurhour101]

Brilliant thanks guys, that is working!

 

[Fluff]

Glad we could help & thanks for the feedback.