rsutton1981
New Member
- Joined
- Mar 9, 2016
- Messages
- 47
- Office Version
- 365
- Platform
- Windows
I have a sheet which a user selected a risk from a drop down box. On pressing a run button I want the search for the value in B6 down, find any values of the same in a sheet called Database and copy the information to a different sheet. I have created the following code but it only copies the last line in the database for the relevant value in column B and not all copies of the data. If then also does not then look up the next search value in column B.
Any help will be appreciated.
Thank
Any help will be appreciated.
Thank
VBA Code:
Sub RUN_Risk_Selection()
Sheets("Risk Assessment").Range("A2:AA10000").Clear
Call CDMRiskbatch1
Call CDMRiskbatch2
End Sub
Sub CDMRiskbatch1()
Dim Bcell As Range
Dim NextRow
'Surveying at height
For Each Bcell In Sheets("Risk Selection").Range("B6", Sheets("Risk Selection").Range("B" & Rows.Count).End(xlUp))
If Bcell.Value = "Surveying at Height" Then
NextRow = Sheets("Risk Assessment").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Risk Assessment").Range("A" & NextRow) = Sheets("DataBase").Range("A" & Bcell.Row)
Sheets("Risk Assessment").Range("B" & NextRow) = Sheets("DataBase").Range("B" & Bcell.Row)
Sheets("Risk Assessment").Range("C" & NextRow) = Sheets("DataBase").Range("C" & Bcell.Row)
Sheets("Risk Assessment").Range("D" & NextRow) = Sheets("DataBase").Range("D" & Bcell.Row)
Sheets("Risk Assessment").Range("E" & NextRow) = Sheets("DataBase").Range("E" & Bcell.Row)
Sheets("Risk Assessment").Range("F" & NextRow) = Sheets("DataBase").Range("F" & Bcell.Row)
Sheets("Risk Assessment").Range("G" & NextRow) = Sheets("DataBase").Range("G" & Bcell.Row)
Sheets("Risk Assessment").Range("H" & NextRow) = Sheets("DataBase").Range("H" & Bcell.Row)
Sheets("Risk Assessment").Range("I" & NextRow) = Sheets("DataBase").Range("I" & Bcell.Row)
Sheets("Risk Assessment").Range("J" & NextRow) = Sheets("DataBase").Range("J" & Bcell.Row)
Sheets("Risk Assessment").Range("K" & NextRow) = Sheets("DataBase").Range("K" & Bcell.Row)
Sheets("Risk Assessment").Range("L" & NextRow) = Sheets("DataBase").Range("L" & Bcell.Row)
Sheets("Risk Assessment").Range("M" & NextRow) = Sheets("DataBase").Range("M" & Bcell.Row)
Sheets("Risk Assessment").Range("N" & NextRow) = Sheets("DataBase").Range("N" & Bcell.Row)
Sheets("Risk Assessment").Range("O" & NextRow) = Sheets("DataBase").Range("O" & Bcell.Row)
Sheets("Risk Assessment").Range("P" & NextRow) = Sheets("DataBase").Range("P" & Bcell.Row)
' Sheets("Risk Assessment").Range("Q" & NextRow) = Sheets("DataBase").Range("Q" & Bcell.Row)
' Sheets("Risk Assessment").Range("R" & NextRow) = Sheets("DataBase").Range("R" & Bcell.Row)
' Sheets("Risk Assessment").Range("S" & NextRow) = Sheets("DataBase").Range("S" & Bcell.Row)
' Sheets("Risk Assessment").Range("T" & NextRow) = Sheets("DataBase").Range("T" & Bcell.Row)
' Sheets("Risk Assessment").Range("U" & NextRow) = Sheets("DataBase").Range("U" & Bcell.Row)
' Sheets("Risk Assessment").Range("V" & NextRow) = Sheets("DataBase").Range("V" & Bcell.Row)
' ' Call formatting_dates
End If
Next Bcell
End Sub
Sub CDMRiskbatch2()
Dim Bcell As Range
Dim NextRow
'Surveying unstable buildings/sites
For Each Bcell In Sheets("Risk Selection").Range("B6", Sheets("Risk Selection").Range("B" & Rows.Count).End(xlUp))
If Bcell.Value = "Surveying unstable buildings/sites" Then
NextRow = Sheets("Risk Assessment").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Risk Assessment").Range("A" & NextRow) = Sheets("DataBase").Range("A" & Bcell.Row)
Sheets("Risk Assessment").Range("B" & NextRow) = Sheets("DataBase").Range("B" & Bcell.Row)
Sheets("Risk Assessment").Range("C" & NextRow) = Sheets("DataBase").Range("C" & Bcell.Row)
Sheets("Risk Assessment").Range("D" & NextRow) = Sheets("DataBase").Range("D" & Bcell.Row)
Sheets("Risk Assessment").Range("E" & NextRow) = Sheets("DataBase").Range("E" & Bcell.Row)
Sheets("Risk Assessment").Range("F" & NextRow) = Sheets("DataBase").Range("F" & Bcell.Row)
Sheets("Risk Assessment").Range("G" & NextRow) = Sheets("DataBase").Range("G" & Bcell.Row)
Sheets("Risk Assessment").Range("H" & NextRow) = Sheets("DataBase").Range("H" & Bcell.Row)
Sheets("Risk Assessment").Range("I" & NextRow) = Sheets("DataBase").Range("I" & Bcell.Row)
Sheets("Risk Assessment").Range("J" & NextRow) = Sheets("DataBase").Range("J" & Bcell.Row)
Sheets("Risk Assessment").Range("K" & NextRow) = Sheets("DataBase").Range("K" & Bcell.Row)
Sheets("Risk Assessment").Range("L" & NextRow) = Sheets("DataBase").Range("L" & Bcell.Row)
Sheets("Risk Assessment").Range("M" & NextRow) = Sheets("DataBase").Range("M" & Bcell.Row)
Sheets("Risk Assessment").Range("N" & NextRow) = Sheets("DataBase").Range("N" & Bcell.Row)
Sheets("Risk Assessment").Range("O" & NextRow) = Sheets("DataBase").Range("O" & Bcell.Row)
Sheets("Risk Assessment").Range("P" & NextRow) = Sheets("DataBase").Range("P" & Bcell.Row)
' Sheets("Risk Assessment").Range("Q" & NextRow) = Sheets("DataBase").Range("Q" & Bcell.Row)
' Sheets("Risk Assessment").Range("R" & NextRow) = Sheets("DataBase").Range("R" & Bcell.Row)
' Sheets("Risk Assessment").Range("S" & NextRow) = Sheets("DataBase").Range("S" & Bcell.Row)
' Sheets("Risk Assessment").Range("T" & NextRow) = Sheets("DataBase").Range("T" & Bcell.Row)
' Sheets("Risk Assessment").Range("U" & NextRow) = Sheets("DataBase").Range("U" & Bcell.Row)
' Sheets("Risk Assessment").Range("V" & NextRow) = Sheets("DataBase").Range("V" & Bcell.Row)
' ' Call formatting_dates
End If
Next Bcell
End Sub