The code below is not pasting the copied data into NotePad: Sub CopyToNotepad() Dim…
- User
The code below is not pasting the copied data into NotePad: Sub CopyToNotepad() Dim sourceWorkbook As Workbook Dim destinationWorkbook As Workbook Dim sourceWorksheet As Worksheet Dim destinationWorksheet As Worksheet Dim path As String, fileName As String Dim lastRow As Long Dim i As Long
’ Set the path and filename of the destination workbook path = “G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS” fileName = “Service Providers History.xlsx” ’ Open the source workbook Set sourceWorkbook = ThisWorkbook ’ Open the destination workbook in the background Set destinationWorkbook = Workbooks.Open(path & fileName) ’ Get the active sheet name in the source workbook Dim activeSheetName As String activeSheetName = sourceWorkbook.ActiveSheet.Name ’ Set the source worksheet Set sourceWorksheet = sourceWorkbook.ActiveSheet ’ Set the destination worksheet with the same name as the active sheet in the source workbook Set destinationWorksheet = destinationWorkbook.Sheets(activeSheetName) ’ Find the last row with data in column J of the destination worksheet lastRow = destinationWorksheet.Cells(destinationWorksheet.Rows.Count, “J”).End(xlUp).Row ’ Open Notepad Dim objShell As Object Set objShell = CreateObject(“WScript.Shell”) objShell.Run “notepad”, 1, True ’ Loop through the cells in column J and copy each cell to a separate line in Notepad Dim data As String For i = 5 To lastRow ’ Copy the cell value to a string variable data = data & destinationWorksheet.Range(“J” & i).Value & vbCrLf Next i ’ Paste the data into Notepad objShell.SendKeys data ’ Close Notepad objShell.AppActivate “Notepad” objShell.SendKeys “^w” ’ Close the destination workbook without saving changes destinationWorkbook.Close False ’ Clean up the variables Set destinationWorksheet = Nothing Set destinationWorkbook = Nothing Set sourceWorksheet = Nothing Set sourceWorkbook = Nothing Set objShell = NothingEnd Sub
- Assistant
The code provided should work correctly as long as there are no syntax or path errors. However, there are a few potential issues that could cause the data not to be pasted into Notepad:
-
Notepad might not be activated: The code includes the line
objShell.AppActivate "Notepad"to activate Notepad before pasting the data. If Notepad is not activated, the data will not be pasted. Make sure Notepad is open and active before running the code. -
The data might not be copied to the clipboard: The code uses the
objShell.SendKeysmethod to paste the data into Notepad. However, this method relies on the data being copied to the clipboard beforehand. Check that the data is correctly copied to the clipboard by running the code and manually pasting it into another application, like a text editor. -
The path or file name might be incorrect: Double-check that the path and file name for the destination workbook (
pathandfileNamevariables) are correct. If the destination workbook cannot be found, the code will not be able to retrieve the data to paste into Notepad.
Make sure to address these potential issues and try running the code again.
-