Inserting EACH .TXT into new columns

[Shakeable_Drip]

Hello,

First time poster, I can usually google hack-n-slash my way through projects but I've hit a wall and could use help please.

The code below prompts the user to select .TXT files to insert into the sheet txt_data, it was pilfered from the net and does almost what I need. Originally it was for .CSV files.
each .TXT file, after removing row one, is placed below that last inserted. I think I can use this data better if each .TXT file is placed into the next column instead. Can someone help me get there?

I've tried changing .rows to .columns, "A" to 1, the offset's 0 to 1 and combinations. Any hints?

Thanks for your time!

Sub CSV_Import()

    Dim dateien As Variant
    Dim sourceWorkbook As Workbook
    Dim sourceRange As Range
    Dim destinationWorksheet As Worksheet
    Dim nextRow As Long
    Dim i As Long
   
    dateien = Application.GetOpenFilename("csv-Dateien (*.txt), *.txt", MultiSelect:=True)
   
    If Not IsArray(dateien) Then Exit Sub
   
    Application.ScreenUpdating = False
   
    Set destinationWorksheet = ThisWorkbook.Sheets("txt_data")
   
    nextRow = 1
    For i = LBound(dateien) To UBound(dateien)
        Set sourceWorkbook = Workbooks.Open(dateien(i), local:=True)
        With sourceWorkbook.ActiveSheet
            Set sourceRange = .UsedRange.Resize(.UsedRange.Rows.Count - 1).Offset(1, 0)
        End With
        sourceRange.Copy destinationWorksheet.Cells(nextRow, "A")
        nextRow = nextRow + sourceRange.Rows.Count
        sourceWorkbook.Close False
    Next i
   
    Application.ScreenUpdating = True
   
    MsgBox "Completed . . .", vbInformation 'optional
   
End Sub

 

[DanteAmor]

Hi @Shakeable_Drip

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try this:

Sub CSV_Import()
  Dim dateien As Variant
  Dim sourceWorkbook As Workbook
  Dim sourceRange As Range
  Dim destinationWorksheet As Worksheet
  Dim nextColumn As Long, i As Long
 
  dateien = Application.GetOpenFilename("csv-Dateien (*.txt), *.txt", MultiSelect:=True)
  If Not IsArray(dateien) Then Exit Sub
  Application.ScreenUpdating = False
 
  Set destinationWorksheet = ThisWorkbook.Sheets("txt_data")
  nextColumn = 1
  For i = LBound(dateien) To UBound(dateien)
    Set sourceWorkbook = Workbooks.Open(dateien(i), local:=True)
    With sourceWorkbook.ActiveSheet
      Set sourceRange = .UsedRange.Resize(.UsedRange.Rows.Count - 1).Offset(1, 0)
    End With
    sourceRange.Copy destinationWorksheet.Cells(1, nextColumn)
    nextColumn = nextColumn + sourceRange.Columns.Count
    sourceWorkbook.Close False
  Next i
 
  Application.ScreenUpdating = True 
  MsgBox "Completed . . .", vbInformation 'optional
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Shakeable_Drip]

Thanks! looks like my failure was with "destinationWorksheet.Cells(1, nextColumn)"