Code Modification

[erutherford]

This code was provided to me by this forum and it works perfect. I want to expand it, but I have tried lots of different mods to it unsuccessfully.

The goal is to allow any number of columns to be copied over to "Hstry" worksheet, based on the valve ("4"), column H on sheet "WO"

What controls the limit in the number of columns to be copied over to "Hstry" sheet or does it? The unbound (Data),1 to 8 is what I don't quite get.

Private Sub CommandButton1_Click()
Dim r As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "H").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 8)
  
 X = Sheets("WO").Range("A" & Rows.Count).End(xlUp).Row

For r = 1 To UBound(Data)
    If Data(r, 8) = "4" Then ' This controls what column get copied
        X = X + 1
        With Sheets("Hstry")
            .Cells(X, 1).Value = Data(r, 2) 'Rpt-Chklst-Col.B to WO-Col.A
            .Cells(X, 2).Value = Data(r, 3) 'Rpt-Chklst-Col.C to WO-Col.B
            .Cells(X, 3).Value = Data(r, 4) 'Rpt-Chklst-Col.D to WO-Col.C
            .Cells(X, 8).Value = Data(r, 8) 'Rpt-Chklst-Col.H to WO-Col.H
        End With
    End If
Next r
MsgBox "Data Transfered"
End Sub

 

[Fluff]

In that case try

Private Sub CommandButton1_Click()
Dim r As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "H").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 8)
  
 X = Sheets("Hstry").Range("A" & Rows.Count).End(xlUp).Row

For r = 1 To UBound(Data)
    If Data(r, 8) = "4" Then ' This controls what column get copied
        X = X + 1
        With Sheets("Hstry")
            .Cells(X, 1).Resize(, 7).Value = Application.Index(Data, r, 0) 'Rpt-Chklst-Col.B to WO-Col.A
        End With
    End If
Next r
MsgBox "Data Transfered"
End Sub

 

[erutherford]

Fluff,
Its works perfect. What or where is the control of the number of columns copied?
Resize(, 7).Value - Is 7 the number of columns?

Thanks

 

[Fluff]

Yes it is.