How to copy non contiguous cells to notepad

Hello kati8293,

Welcome to the Forum!

When the columns are pasted do you want the values to be in 2 columns separated by spaces or tabs or what?

Do you want an empty field (blank) pasted for cells B6:B7 or paste C6:C7 in the first field position?

Hello kati8293,

This macro will copy the data directly to a file and save it. This is easier than first opening Notepad and copying the data to it and closing. Change the file path and name to want you will be using. This is marked in bold text in the macro code.

'Thread:  http://www.excelforum.com/excel-programming/784879-how-to-copy-non-contiguous-cells-to-notepad.html
'Poster:  kati8293
'Written: July 19, 2011
'Author:  Leith Ross
Sub CopyRangeToTextFile()

  Dim Cell As Range
  Dim Data As Variant
  Dim Filespec As String
  Dim N As Integer
  Dim rngArea As Range
  Dim rngRow As Range
  Dim Rng As Range
  
    Filespec = "C:\Test File.txt"
    
    N = FreeFile
    
    Set Rng = ActiveSheet.Range("B5:C5,C6:C7,B8:C23")
    
    For Each rngArea In Rng.Areas
    
      For Each rngRow In rngAreas.Rows
      
        Data = rngRow.Cells.Value
        
        Open Filespec For Output As #N
          If Data(1, 2) = "" Then Data(1, 1) = ""
          Print #N, Data(1, 1), Spc(1), Data(1, 2)
        Close #N
        
      Next rngRow
      
    Next rngArea
    
End Sub

Adding the Macro (Excel 95 - 2003)

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

or

Sub snb()
    sn = Range("B5:C23")
    For j = 1 To UBound(sn)
      c01 = c01 & vbCrLf & iif(sn(j,2)="","",IIf(j <> 2 And j <> 3, sn(j, 1), "") & vbTab & sn(j, 2))
    Next
    
    Open "E:\OF\example.txt" For Output As #1
      Print #1, Mid(c01, 3)
    Close
End Sub