Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

This VBA code also has a Function. Have I written both correctly and efficiently: Sub…

  1. 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

  2. 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.