How to grab the information from several cells in a row and input into another sheet

[ecrodrig]

SO I am trying to do the following:

IF K2 in Sheet2 = a certain number then grab the information from S2, F2, H2, I2 from Sheet 2 and input into a new sheet

Does this make sense? Hope someone... I also asked this question here: Excel Help Forum but have not received a response.

 

[offthelip]

try this:

Sub test()
testnumber = 11  '  set a s required
With Worksheets("Sheet2")
 rowdata = .Range("a2:S2").Value ' load the enterie row into a variant array
End With
  If (rowdata(1, 11)) = testnumber Then
    Worksheets.Add
    Range("a1") = rowdata(1, 19) ' S
    Range("b1") = rowdata(1, 6) ' F
    Range("c1") = rowdata(1, 8) ' H
    Range("d1") = rowdata(1, 9) ' I
  End If
   
End Sub

 

[Peter_SSs]

grab the information from S2, F2, H2, I2 from Sheet 2 and input into a new sheet

Assuming that you want those values, in that order in, say, A1:D1 of the new sheet, try this adaptation of the previous code.

BTW, the link to your cross-post does not work for me. Can you try to add a new link in your next post please?

Sub Transfer_Values()
  Dim rowdata As Variant
  Const testnumber As Double = 11  '  set as required
 
  rowdata = Application.Index(Worksheets("Sheet2").Cells, 2, Array(19, 6, 8, 9, 11)) 'read relevant numbers in relevant order (+ test value) into array
  If rowdata(5) = testnumber Then
    Worksheets.Add
    Range("A1:D1") = rowdata
  End If
End Sub

 

[ecrodrig]

try this:

Sub test()
testnumber = 11  '  set a s required
With Worksheets("Sheet2")
 rowdata = .Range("a2:S2").Value ' load the enterie row into a variant array
End With
  If (rowdata(1, 11)) = testnumber Then
    Worksheets.Add
    Range("a1") = rowdata(1, 19) ' S
    Range("b1") = rowdata(1, 6) ' F
    Range("c1") = rowdata(1, 8) ' H
    Range("d1") = rowdata(1, 9) ' I
  End If
  
End Sub

THANKS I will try this!

 

[ecrodrig]

Assuming that you want those values, in that order in, say, A1:D1 of the new sheet, try this adaptation of the previous code.

BTW, the link to your cross-post does not work for me. Can you try to add a new link in your next post please?

Sub Transfer_Values()
  Dim rowdata As Variant
  Const testnumber As Double = 11  '  set as required
 
  rowdata = Application.Index(Worksheets("Sheet2").Cells, 2, Array(19, 6, 8, 9, 11)) 'read relevant numbers in relevant order (+ test value) into array
  If rowdata(5) = testnumber Then
    Worksheets.Add
    Range("A1:D1") = rowdata
  End If
End Sub

THANKS I will try this.