I have two VBA codes that work perfectly for the outcomes expected. The VBA codes are…
- 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
- 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.
- User
I am getting an error 'Subscript out of range'
- 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:
-
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.
-
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.
-
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 SubNote: 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.
-
- User
Thanks, It worked perfectly
- Assistant
You're welcome! I'm glad to hear it worked perfectly. If you have any more questions, don't hesitate to ask.