In an excel Sheet Job Request When I choose a value from a list using a form button on…
- User
In an excel Sheet Job Request When I choose a value from a list using a form button on the Job Request Sheet I want the sheet to calculate, then 4 seconds later I want to copy at most, the last 10 cells with values fom column J of the sheet named in I2 of Job Request, and paste the copied values into a new text document line by line and have focus, activate the text document
- Assistant
for easy editing.
To accomplish this:
- Select the cell where the calculated result will appear.
- Go to the Developer tab and click on Insert button.
- Select the “Button” Control from the “Form Controls”.
- Drag it to your preferred place on the Job Request sheet.
- Right-click on the newly created Button and select “Assign Macro…”
- Give a name for the macro, and click on “Create”
- In the new VBA project window, paste the following code:
Sub Generate_Text_Snapshot() ' Get the name of the sheet to be copied Dim sheetName As String sheetName = Range("I2").Value ' Copy the last 10 cells of column J of the specified sheet Dim copyRange As Range Set copyRange = Worksheets(sheetName).Range("J" & Rows.count).End(xlUp).Resize(10, 1) copyRange.Copy ' Create a new text document and paste the copied values Dim newDoc As Object Set newDoc = CreateObject("Word.Document") newDoc.Application.Visible = True newDoc.Content.PasteSpecial DataType:=wdPasteText ' Activate the new text document newDoc.Activate End Sub- Save the VBA project and close the window.
- Click on the Button you created and see the text document being generated.
Note: The code assumes that you have Microsoft Word installed and that you have a default text editor set up. If this is not the case, you will need to modify the code accordingly.
- User
i want the document used to be notepad
- Assistant
To make the code generate a text document in Notepad instead of Word, you can modify the code snippet as follows:
Sub Generate_Text_Snapshot() ' Get the name of the sheet to be copied Dim sheetName As String sheetName = Range("I2").Value ' Copy the last 10 cells of column J of the specified sheet Dim copyRange As Range Set copyRange = Worksheets(sheetName).Range("J" & Rows.count).End(xlUp).Resize(10, 1) copyRange.Copy ' Create a new text document and paste the copied values Dim shell As Object Set shell = CreateObject("WScript.Shell") shell.Run "notepad.exe", 1, True ' open notepad Application.Wait Now + TimeValue("0:00:02") ' wait 4 seconds shell.SendKeys "^v" ' paste copied values shell.SendKeys "{ENTER}" ' move to a new line End SubThis code launches Notepad using the Windows Scripting Host object, waits for 4 seconds using the
Application.Waitmethod, and pastes the copied values into Notepad using theshell.SendKeysmethod. Note that this method injects keystrokes into the active window, so make sure that Notepad has focus before running the macro. - User
for each row with values found in column J, paste into a separate line in the text document
- Assistant
To modify the code so that each row with a value found in column J is pasted into a separate line in the text document:
Sub Generate_Text_Snapshot() ' Get the name of the sheet to be copied Dim sheetName As String sheetName = Range("I2").Value ' Get the last row number with data in column J of the specified sheet Dim lastRow As Long lastRow = Worksheets(sheetName).Cells(Rows.count, "J").End(xlUp).Row ' Copy each row with data in column J to a new line in Notepad Dim cellValue As Variant Dim i As Long Dim shell As Object Set shell = CreateObject("WScript.Shell") shell.Run "notepad.exe", 1, True ' open notepad Application.Wait Now + TimeValue("0:00:02") ' wait 4 seconds For i = 1 To lastRow cellValue = Worksheets(sheetName).Range("J" & i).Value If Not IsEmpty(cellValue) Then shell.SendKeys cellValue & vbCrLf ' paste cell value and move to next line End If Next i End SubThis modified code first finds the last row number with data in column J, and then iterates over each row from 1 to this last row number. For each row, if the cell in column J is not empty, it pastes the cell value followed by a line break (
vbCrLf) into Notepad using theshell.SendKeysmethod. This generates a separate line in Notepad for each row with a value found in column J.