looking to loop through a table and return values from first and second column along with eighth and ninth column to a different sheet

Question

The title says most of it what I am looking to create is an inventory management system to handle the inventory for my area but I keep getting runtime errors in my code, I am not super proficient at VBA but I have a base knowledge. Code I am working on is below, any help would be awesome.

Edit: Specific runtime error is 1004: Select Method of Range class failed on line 15 after is has already copy and pasted one.

Sub add2Order()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim C As range, Rng As range, D As range, Rng1 As range

Set Rng = range("K6", range("K6").End(xlDown))

    For Each C In Rng
    
        If InStr(1, C, "X") > 0 Then
            'first select material number and name
            range(C.Offset(0, -9), C.Offset(0, -8)).Select
            Application.CutCopyMode = False
            Selection.Copy
            'paste in reorder sheet
            Sheets("Re-Order List").Select
            Selection.End(xlDown).Select
            range("A65536").Select
            Selection.End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = True
        End If
    Next
    
Set Rng1 = range("K6", range("K6").End(xlDown))
    
    For Each D In Rng1
    
    If InStr(1, D, "X") > 0 Then
            'Second select amount and cost
            range(D.Offset(0, -2), D.Offset(0, -1)).Select
            Application.CutCopyMode = False
            Selection.Copy
            'paste in reorder sheet
            Sheets("Re-Order List").Select
            Selection.End(xlDown).Select
            range("C65536").Select
            Selection.End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = True
        End If
    Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Inventory sheet to copy from

Reorder sheet to paste in

[Don''t Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Warcupine, Feb 11 '21 at 16:17
The link explains it, you just refer directly to the range object instead of selecting it and then referring to the selection. — Warcupine, Feb 11 '21 at 16:25

score 2 · Answer 1 · answered Feb 11 '21 at 17:00

If you remove "select" your macro should look like this:

Sub add2Order()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim C As Range, Rng As Range, D As Range, Rng1 As Range

Set Rng = Range("K6", Range("K6").End(xlDown))

 For Each C In Rng

     If InStr(1, C, "X") > 0 Then
        'first select material number and name
        Application.CutCopyMode = False
        Range(C.Offset(0, -9), C.Offset(0, -8)).Copy
        'paste in reorder sheet
        Sheets("Re-Order List").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = True
    End If
    Next

Set Rng1 = Range("K6", Range("K6").End(xlDown))

    For Each D In Rng1

    If InStr(1, D, "X") > 0 Then
        'Second select amount and cost
        Application.CutCopyMode = False
        Range(D.Offset(0, -2), D.Offset(0, -1)).Copy
        'paste in reorder sheet
        Sheets("Re-Order List").Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
        Application.CutCopyMode = True
    End If
    Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

If you run a macro on sheets (1) and You do "select" or "active" sheets (2) in your code, excel has a problem because both sheets are therefore indicated as active.

looking to loop through a table and return values from first and second column along with eighth and ninth column to a different sheet

1 Answers1