KCPrescott
New Member
- Joined
- May 5, 2016
- Messages
- 2
I have excel file with several hundred entries.
Sheet 1 contains the main list of all the Companies formatted by:
Column A=User ID#
Column B=Company
In Column E there is a list of selected companies noted by their ID#
<tbody>
</tbody>
I need a macro that will search Column A for all variables in Column E and then copy that row to Sheet 2.
So from the sample above Sheet 2 would look like:
<tbody>
</tbody>
I tried to modify this code:
Sub copyrows()
Dim tfCol As Range, Cell As Object
Set tfCol = Range("A2:A500") 'Substitute with the range which includes your True/False values
For Each Cell In tfCol
If IsEmpty(Cell) Then
Exit Sub
End If
If Cell.Value = ("3") Then 'Substitute with the Selected User ID
Cell.EntireRow.Copy
Sheet2.Select 'Substitute with your sheet
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If
Next
End Sub
However this only lets me put in one value at a time. Is there a way to have it automatically use all the values from a specific column or to enter all the values into the code at one time?
Like: If Cell.Value = ("Search Column E for all values") Then
(Replacing the text in red for actual code)
Or: If Cell.Value = ("3" "5" "7"..."65") Then
(Replacing the text in red for actual code)
It seems like it should be feasible but I am new to excel and how to go about using macros and formulas. Please help.
Sheet 1 contains the main list of all the Companies formatted by:
Column A=User ID#
Column B=Company
In Column E there is a list of selected companies noted by their ID#
User ID | Company | Country | Selected User ID | |
1 | Company1 | US | 3 | |
2 | Company2 | China | 5 | |
3 | Company3 | Canada | 7 | |
4 | Company4 | US | 19 | |
5 | Company5 | US | 44 | |
6 | Company6 | US | 65 |
<tbody>
</tbody>
I need a macro that will search Column A for all variables in Column E and then copy that row to Sheet 2.
So from the sample above Sheet 2 would look like:
User Id | Company | Country | Selected User ID | |
3 | Company3 | Canada | 7 | |
5 | Comany5 | US | 44 |
<tbody>
</tbody>
I tried to modify this code:
Sub copyrows()
Dim tfCol As Range, Cell As Object
Set tfCol = Range("A2:A500") 'Substitute with the range which includes your True/False values
For Each Cell In tfCol
If IsEmpty(Cell) Then
Exit Sub
End If
If Cell.Value = ("3") Then 'Substitute with the Selected User ID
Cell.EntireRow.Copy
Sheet2.Select 'Substitute with your sheet
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If
Next
End Sub
However this only lets me put in one value at a time. Is there a way to have it automatically use all the values from a specific column or to enter all the values into the code at one time?
Like: If Cell.Value = ("Search Column E for all values") Then
(Replacing the text in red for actual code)
Or: If Cell.Value = ("3" "5" "7"..."65") Then
(Replacing the text in red for actual code)
It seems like it should be feasible but I am new to excel and how to go about using macros and formulas. Please help.