Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

The code below is not pasting the copied data into NotePad: Sub CopyToNotepad() Dim…

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

    End Sub

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

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

    2. The data might not be copied to the clipboard: The code uses the objShell.SendKeys method 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.

    3. The path or file name might be incorrect: Double-check that the path and file name for the destination workbook (path and fileName variables) 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.