In the interest of clarity, I've wiped the original question and am re-posting.
Scenario:
Source workbook has multiple pages, front page of book has a query/extract function to create a new book with some pre-entered data using a template from one of the sheets in the source book.
Requirements:
Phase 1: Extract function needs to set all rows beyond row 6 as hidden where the data in column A = HC.
First (and so far working) draft of that code as follows:
Sub Extract()
Dim wbkOriginal As Workbook
Set wbkOriginal = ActiveWorkbook
'sets site name and site ID into the estate page to be extracted
Worksheets(Sheet11.CmbSheet.Value).Range("B3").Value = Worksheets("front page").Range("E6")
Worksheets(Sheet11.CmbSheet.Value).Range("D3").Value = Worksheets("front page").Range("N6")
Worksheets(Sheet11.CmbSheet.Value).Range("F3").Value = Worksheets("front page").Range("K6")
'hiding all rows that being with HC apart from row 6 which is the starting row
'code to be added to the individual estate sheets to unhide each row after status column filled
'on a row by row basis - as the hiding is for HC rows only, the section headers will remain visible
'may have to code around that on the sheet itself
BeginRow = 7
EndRow = 300
ChkCol = 1
For RowCnt = BeginRow To EndRow
If Worksheets(Sheet11.CmbSheet.Value).Cells(RowCnt, ChkCol).Value Like "HC" Then
Worksheets(Sheet11.CmbSheet.Value).Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
' copies sheet name from combo box into new document, saves it with site name,
' site id and current date into user profile desktop folder for ease of access
' with new HEAT, worth investigating if sheet can be saved directly to a call ID folder?
With ActiveWorkbook.Sheets(Sheet11.CmbSheet.Value)
.Copy
ActiveWorkbook.SaveAs _
"C:\temp\" _
& .Cells(3, 2).Text _
& " " _
& Format(Now(), "DD-MM-YY") _
& ".xlsm", _
xlOpenXMLWorkbookMacroEnabled, , , , False
End With
'code to close the original workbook to prevent accidental changes etc
Application.DisplayAlerts = False
wbkOriginal.Close
Application.DisplayAlerts = True
End Sub
Phase 2: Each row that starts with HC, has a drop down in column E. That drop down has 3 options, 'Complete' 'Incomplete' and 'Not Required'
Task: When the user selects and clicks on an entry, the sheet needs to do the following
- Unhide the next row
- Enter the current windows username into column I
- Enter the current time into column J
Prototype code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCell As Object
Dim lRow As Long
For Each ChangedCell In Target
If ChangedCell.Column = 5 And ChangedCell <> "" Then
lRow = ChangedCell.Row + 1
lRow.Hidden = False
Cells(lRow, 8) = Environ("USERNAME")
Cells(lRow, 9) = "HH:MM"
End If
Next
End Sub
Problem:
Compile error: Invalid Qualifier, referring to the lRow.Hidden = False line,
Tried to declare it as an object instead, thinking that would allow me to spec it that way instead, but no joy to be had.
As ever, any guidance from the community would be greatly appreciated.
Many thanks.
Rob.
Worksheet_Changeevent. Give it a try and if you are still stuck then post the code that you tried and then we will take it from there - Siddharth Rout