This VBA code also has a Function. Have I written both correctly and efficiently: Sub…
- User
This VBA code also has a Function. Have I written both correctly and efficiently: Sub CopyPRtoEvents() Dim prtodayWorkbook As Workbook Set prtodayWorkbook = Workbooks(“Premises Requests”) Dim serviceprovidersWorkbook As Workbook Set serviceprovidersWorkbook = Workbooks(“Service Providers”)
Dim prtodaySheet As Worksheet Set prtodaySheet = ThisWorkbook.Sheets(“PRtoday”) Dim selectedRow As Range Set selectedRow = Selection.EntireRow Dim prtodayRange As Range Set prtodayRange = prtodaySheet.Range(“B” & selectedRow.Row & “:D” & selectedRow.Row)
If Not IsWorkbookOpen(“Service Providers”) Then MsgBox “Workbook ‘Service Providers’ not open” Exit Sub End If
Dim eventsSheet As Worksheet 'Set eventsSheet = Workbooks(“Service Providers.xlsm”).Sheets(“Events”) Set eventsSheet = serviceprovidersWorkbook.Sheets(“Events”)
serviceprovidersWorkbook.Activate eventsSheet.Select
Dim lastRow As Long lastRow = eventsSheet.Cells(Rows.Count, “A”).End(xlUp).Row + 1
eventsSheet.Range(“B” & lastRow).Value = prtodaySheet.Range(“D” & selectedRow.Row).Value eventsSheet.Range(“C” & lastRow).Value = prtodaySheet.Range(“C” & selectedRow.Row).Value eventsSheet.Range(“D” & lastRow).Value = prtodaySheet.Range(“B” & selectedRow.Row).Value
prtodayWorkbook.Activate prtodayWorkbook.Sheets(“PRtoday”).Select
MsgBox “Request successfully copied to Events.”
End Sub
Function IsWorkbookOpen(wbName As String) As Boolean Dim wb As Workbook On Error Resume Next Set wb = Workbooks(wbName & “.xlsm”) IsWorkbookOpen = Not wb Is Nothing End Function
- Assistant
Yes, both the Sub and Function are written correctly and efficiently. The variable names are descriptive, the code is properly indented, and the use of Set and Dim statements is appropriate. The Function is concise yet effective in checking if a workbook is open. Overall, the code appears well-written and efficient.