Barryj
02-22-2015, 10:49 PM
I am using the below code to search column B for a value that appears in H1 on sheet1, it then copys the entire row to sheet3, I want this macro to be able to search multipule columns and copy four columns of data for each range.
Private Sub commandbutton1_click()
With Excel.ThisWorkbook.Sheets("Sheet1")
Dim cell
For Each cell In .Range(.Cells(2, 2), Cells(.Rows.Count, 1).End(Excel.xlUp))
If UCase(cell(1, 1)) = Sheets("Sheet1").Range("H1") Then
With Excel.ThisWorkbook.Sheets("Sheet2")
Cells(1, 4).Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
If UCase(cell(1, 1)) = .Range("H1") Then
With Excel.ThisWorkbook.Sheets("Sheet3")
Cells(1, 4).Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
Next
End With
End Sub
What I want to do is the following:
Search column B for value in H1 copy data range A to D
Search Column G for value in H1 copy data range F to I
Search Column L for value in H1 copy data range K to N
Search Column Q for value in H1 copy data range P to S
I have attached a workbook showing the input and output data.
Sheet 1 has the input data and sheet 3 the desired output results
Thanks for any assistance or guideance
Private Sub commandbutton1_click()
With Excel.ThisWorkbook.Sheets("Sheet1")
Dim cell
For Each cell In .Range(.Cells(2, 2), Cells(.Rows.Count, 1).End(Excel.xlUp))
If UCase(cell(1, 1)) = Sheets("Sheet1").Range("H1") Then
With Excel.ThisWorkbook.Sheets("Sheet2")
Cells(1, 4).Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
If UCase(cell(1, 1)) = .Range("H1") Then
With Excel.ThisWorkbook.Sheets("Sheet3")
Cells(1, 4).Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
Next
End With
End Sub
What I want to do is the following:
Search column B for value in H1 copy data range A to D
Search Column G for value in H1 copy data range F to I
Search Column L for value in H1 copy data range K to N
Search Column Q for value in H1 copy data range P to S
I have attached a workbook showing the input and output data.
Sheet 1 has the input data and sheet 3 the desired output results
Thanks for any assistance or guideance