Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

I have two VBA codes that work perfectly for the outcomes expected. The VBA codes are…

  1. User

    I have two VBA codes that work perfectly for the outcomes expected. The VBA codes are listed below but relate to completely different sheets. I would like to combine them to accomplish the following outcome:

    Workbook 'Service Providers' contains the sheet 'Events' Workbook 'Premises Requests' contains the sheet 'PRtoday'

    I would like to select a cell in a row in the sheet 'PRtoday' then run a vba code that will do the following:

    It will check if the Workbook 'Service Providers' is open or not. If it is not open, it will Pop Up a message "Workbook 'Service Providers' not open" then it will exit the sub. If workbook 'Service Providers' is open, the cell row selected of the row in 'Workbook 'Premises Requests' sheet 'Prtoday' from Row B:D will be copied to the other open 'Workbook 'Service Providers' sheet 'Events' and pasted into the next available empty row in the following order, PRtoday column D will be pasted into Events column A PRtoday column C will be pasted into Events column C PRtoday column B will be pasted into Events column D After the paste a message will pop up saying 'Request sucessfully copied to Events'

    Here are the two working VBA codes that could possibly be combined to provide the desired outcome:

    Sub PremisesRequestToNote() If Selection.Cells.Count = 1 Then Dim noteText As Range Set noteText = Range("B" & Selection.Row & ":E" & Selection.Row) Dim cell As Range Dim text As String For Each cell In noteText text = text & cell.Value & vbCrLf Next cell Shell "notepad.exe", vbNormalFocus SendKeys text Application.CutCopyMode = False End If Application.CutCopyMode = True End Sub

    Sub CopyRemindersToEvents()

    Dim remindersSheet As Worksheet Set prtodaySheet = ThisWorkbook.Sheets("PRtoday")

    Dim eventsSheet As Worksheet Set eventsSheet = Workbook("Service Providers").Sheets("Events")

    Dim lastRow As Long lastRow = remindersSheet.Cells(Rows.Count, "E").End(xlUp).Row

    Dim i As Long For i = 23 To lastRow If Not IsEmpty(prtodaySheet.Range("B" & i).Value) And _ Not IsEmpty(prtodaySheet.Range("C" & i).Value) And _ Not IsEmpty(prtodaySheet.Range("D" & i).Value) Then Dim destinationRow As Long destinationRow = eventsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    eventsSheet.Range("A" & destinationRow).Value = prtodaySheet.Range("D" & i).Value 'Event A is Date eventsSheet.Range("C" & destinationRow).Value = prtodaySheet.Range("C" & i).Value 'Event C is Details eventsSheet.Range("D" & destinationRow).Value = prtodaySheet.Range("B" & i).Value 'Event D is Notes End If Next i

    Range("B22").Value = Now()

    End Sub

  2. Assistant

    Here is a combined VBA code that should accomplish the desired outcome:

    Sub CopyPRtoEvents() 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.xlsx").Sheets("Events")
    
    Dim lastRow As Long
    lastRow = eventsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    eventsSheet.Range("A" & 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
    
    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 & ".xlsx") IsWorkbookOpen = Not wb Is Nothing End Function

    This code first checks if the 'Service Providers' workbook is open using the IsWorkbookOpen function. If it is not open, a message box is displayed and the sub ends. If it is open, the selected row from 'PRtoday' is copied and pasted into the next available row in the 'Events' sheet of the 'Service Providers' workbook. A message box is displayed after the paste is complete.

  3. User

    I am getting an error 'Subscript out of range'

  4. Assistant

    The "Subscript out of range" error can occur for different reasons, but one possible cause in this case could be that the name of the workbook or worksheet is not correct. Here are a few things you can check:

    1. Make sure that the name of the workbook 'Service Providers.xlsx' is exactly the same, including capitalization and file extension. If the name is not correct, you can modify it in the code.

    2. Make sure that the workbook 'Service Providers.xlsx' is open before running the macro. Otherwise, the "Subscript out of range" error will be triggered because VBA cannot find the workbook. You can manually open the file or add code to check if the workbook is open and open it if necessary.

    3. Make sure that the worksheet name "PRtoday" and "Events" are correct. If the worksheet names are not correct, you can modify them in the code.

    Here is an updated code that includes a check to make sure the 'Service Providers' workbook is open before attempting to access it:

    Sub CopyPRtoEvents()
        Dim selectedRow As Range
        Set selectedRow = Selection.EntireRow
        
        ' Check if 'Service Providers' workbook is open
        Dim wb As Workbook
        On Error Resume Next
        Set wb = Workbooks("Service Providers.xlsx")
        On Error GoTo 0
        If wb Is Nothing Then
            MsgBox "Workbook 'Service Providers.xlsx' is not open."
            Exit Sub
        End If
        
        Dim prtodaySheet As Worksheet
        Set prtodaySheet = ThisWorkbook.Sheets("PRtoday")
        Dim prtodayRange As Range
        Set prtodayRange = prtodaySheet.Range("B" & selectedRow.Row & ":D" & selectedRow.Row)
        
        Dim eventsSheet As Worksheet
        Set eventsSheet = wb.Sheets("Events")
        
        Dim lastRow As Long
        lastRow = eventsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        eventsSheet.Range("A" & 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
        
        MsgBox "Request successfully copied to Events."
    End Sub
    

    Note: If the error still persists, please let me know which line is causing the error and if you can provide more information or screenshots of your workbook.

  5. User

    Thanks, It worked perfectly

  6. Assistant

    You're welcome! I'm glad to hear it worked perfectly. If you have any more questions, don't hesitate to ask.