none
Transpose Range and Transfer to Notepad RRS feed

  • Question

  • Hi excel experts!

    I have an excel worksheet that I copy certain values from and paste them onto Notepad. For example as below, I would

    1. filter the list to show only the rows highlighted in green
    2. open Notepad
    3. copy three column headers from the excel sheet, "Date", "Slot Name" and "Programme Name"
    3. paste them onto Notepad VERTICALLY
    4. copy the cell value of each filtered (green) row for those three columns, and paste it next to the header in Notepad
    5. Save Notepad

    I'm not very familiar with VBA but heard something like this could be achieved with VBA code. 

    This example sheet has only three rows to transfer, but with a larger list, i'm feeling really frustrated having to copy and paste one by one... Help me please~~~

    Thank you!

    Jay



    • Edited by jay.nz Tuesday, June 19, 2018 1:53 AM
    Tuesday, June 19, 2018 1:52 AM

Answers

  • Hello jay.nz,

    You could do something like below code. Please note it will do the work to write to a text file silently, you could check the content in the Test.text after running the code.

    Sub Macro3()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim txtFilePath As String
    txtFilePath = "C:\Users\admin\Desktop\TestFolder\Test.txt"
    
    Dim DateString As String * 20
    Dim SlotNameString As String * 20
    Dim ProgrammeNameString As String * 20
    DateString = ws.Cells(1, 1)
    SlotNameString = ws.Cells(1, 2)
    ProgrammeNameString = ws.Cells(1, 3)
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    ws.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(112, _
            173, 71), Operator:=xlFilterCellColor
    Dim r As Range
    
    Open txtFilePath For Output As #1
    For Each r In ws.UsedRange.Rows
    If r.Row > 1 And r.EntireRow.Hidden = False Then
    Print #1, DateString & r.Cells(1, 1)
    Print #1, SlotNameString & r.Cells(1, 2)
    Print #1, ProgrammeNameString & r.Cells(1, 3)
    Print #1, ""
    End If
    Next r
    Close #1
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Tuesday, June 19, 2018 8:58 AM
    Tuesday, June 19, 2018 6:46 AM

All replies

  • Hello jay.nz,

    You could do something like below code. Please note it will do the work to write to a text file silently, you could check the content in the Test.text after running the code.

    Sub Macro3()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim txtFilePath As String
    txtFilePath = "C:\Users\admin\Desktop\TestFolder\Test.txt"
    
    Dim DateString As String * 20
    Dim SlotNameString As String * 20
    Dim ProgrammeNameString As String * 20
    DateString = ws.Cells(1, 1)
    SlotNameString = ws.Cells(1, 2)
    ProgrammeNameString = ws.Cells(1, 3)
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    ws.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(112, _
            173, 71), Operator:=xlFilterCellColor
    Dim r As Range
    
    Open txtFilePath For Output As #1
    For Each r In ws.UsedRange.Rows
    If r.Row > 1 And r.EntireRow.Hidden = False Then
    Print #1, DateString & r.Cells(1, 1)
    Print #1, SlotNameString & r.Cells(1, 2)
    Print #1, ProgrammeNameString & r.Cells(1, 3)
    Print #1, ""
    End If
    Next r
    Close #1
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Tuesday, June 19, 2018 8:58 AM
    Tuesday, June 19, 2018 6:46 AM
  • Thank you very much Terry!

    Works like magic!

    Took me quite a while tweaking and adding some more code here and there to actually apply to my master sheet. It works perfectly though. Just trying to figure out how to give a text file a name with today's date and time, so it doesn't get overwritten. I'm getting there slowly. 

    Thank you very much for your help, Terry. 

    Jay

    Tuesday, June 19, 2018 9:05 AM