crazzyapple
New Member
- Joined
- Jan 22, 2021
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
Hello;
I have this VBA code that searches a sheet called Data, which is based on search criteria that is located in Cell P2 on Sheet VRF and copies it under P2. It works fine. However, I would like to search all sheets and copy. I found this code on the Microsoft Support webpage to loop through multiple worksheets, but I am not sure on how to combine them. If someone could help me, that would be greatly appreciated.
I have this VBA code that searches a sheet called Data, which is based on search criteria that is located in Cell P2 on Sheet VRF and copies it under P2. It works fine. However, I would like to search all sheets and copy. I found this code on the Microsoft Support webpage to loop through multiple worksheets, but I am not sure on how to combine them. If someone could help me, that would be greatly appreciated.
VBA Code:
Sub SearchData()
Dim employeename As String
Dim finalrow As Integer
Dim i As Integer
Sheets("VRF").Range("P3:Z100").ClearContents
employeename = Sheets("VRF").Range("P2").Value
finalrow = Sheets("Data").Range("A1000").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 1) = employeename Then
Range(Cells(i, 2), Cells(i, 11)).Copy
Sheets("VRF").Range("P100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
End Sub
VBA Code:
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' 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.
MsgBox ActiveWorkbook.Worksheets(I).Name
Next I
End Sub