Excel Forum

Code: Select all

Public Sub CreateText()
Dim n As Integer
Dim NoteP As Double
n = Application.WorksheetFunction.CountA(Range("A5:A1000")) + 4
Range(Cells(5, 1), Cells(n, 29)).Copy
NoteP = Shell("Notepad.exe", vbNormalFocus)
AppActivate NoteP
SendKeys "^v"
'Code Save notepad
End Sub

Code: Select all

Public Sub CreateText()
    Dim n As Integer
    Dim NoteP As Double
    Dim wb As Workbook
    
    Workbooks.Add
    Set wb = ActiveWorkbook
    ThisWorkbook.Activate
    n = Application.WorksheetFunction.CountA(Range("A5:A1000")) + 4
    Range(Cells(5, 1), Cells(n, 29)).Copy _
        wb.Sheets(1).Range("a1")
    
    wb.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now, "yymmdd-hhmm") _
        & ThisWorkbook.Name & "_" & ActiveSheet.Name & ".txt", _
        FileFormat:=xlText, CreateBackup:=False

    'NoteP = Shell("Notepad.exe", vbNormalFocus)
    'AppActivate NoteP
    'SendKeys "^v"
End Sub

Code: Select all

wb.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Now, "yymmdd-hhmm") _
        & ThisWorkbook.Name & "_" & ActiveSheet.Name & ".txt", _
        FileFormat:=xlText, CreateBackup:=False