Hi All; Just one day into VBA and i am stuck into a little something that i could really use help on. Appreciate any/all headways.
What I have: Data in 4 separate tabs in excel(say, A,B,C,D sheets) and a "Control Sheet" having combobox for the column say "Names"
What I am trying to do: Once user selects a "Name" in combobox, data across all the A,B,C,D for "Names" columns must be checked. For the rows that match the Name, four specific columns(Address,Age,location,Phone) from every tab(these columns are named consistently across tabs) must be copied and pasted on the control sheet.
This is what i have so far. Its halfway through what i need but i already have an error on this. Can anyone please help me meet my problem statement?
Here "sample" is my control sheet
Dim WS_Count As Integer
Dim I As Integer, J As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count -1 /* to avoid selecting control sheet*/
With Worksheets("Sample")
X = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
' Begin the loop.
For I = 1 To WS_Count
' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
sheetName = ActiveWorkbook.Worksheets(I).Name
sheetRows = (ActiveWorkbook.Worksheets(I).Range("A" & Rows.Count).End(xlUp).Row)
For J = 1 To sheetRows
If ActiveWorkbook.Worksheets(I).Cells(J, 1).Value = ComboBox1.Value Then
.Rows(J).Copy Destination:=Worksheets("Sample").Range("A" & X)
X = X + 1
End If
Next J
Next I
End Sub
Bookmarks