Very Simple VBA Copy/Paste from delimited file erroneously combines all columns into one

Question

I have multiple files (fanspeedA, fanspeedB....) that do not have any file extensions and are pipe-delimited (or "|"). Could the lack of file extensions cause the VBA to lose its understanding of the delimited columns during copy paste?

Below is the VBA code I'm using, which performs the following steps:

1) Define variables, store the worksheet this macro was called from

2) Open a dialog to select files

3) Start a loop based on file selection, open each file with pipe delimiter "|"

• All files correctly open and are properly delimited with all columns recognized

4) While temporary file is open, copy used range and close file

5) Open original workbook, create new sheet based on temporary file's name, paste cells into this sheet

• Manual copy/paste at this point will retain the columns, but doing this step in VBA combines all columns into the first column

---

Sub loopyarray()

Dim filenames As Variant

' get current workbook name to cut/paste opened sheets into
Dim strBookName As Workbook, tmpBookName As String
Set strBookName = ThisWorkbook

' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)

counter = 1

' ubound determines how many items in the array
While counter <= UBound(filenames)

  ' Opens the selected files
  Workbooks.OpenText filenames(counter), 437, 1, xlDelimited, xlTextQualifierDoubleQuote, 0, 0, 0, 0, 0, 1, "|"

  ' Copy From Temporary Book
  tmpBookName = ActiveSheet.Name 'save temporary sheet name
  ActiveSheet.UsedRange.Select
  Selection.Copy
  ActiveWorkbook.Close

  ' Paste to Original Book
  Windows(strBookName.Name).Activate 'activate original book
  Worksheets.Add(Before:=Worksheets(1)).Name = tmpBookName 'new sheet based on temp sheet name
  Range("A1").Select
  ActiveSheet.Paste

  ' increment counter
  counter = counter + 1

  Wend
End Sub

Edit 1: Changed part of the sub, which is now throwing an error.

' Copy From Temporary Book
      tmpBookName = ActiveSheet.Name 'save temporary sheet name
      Dim rngCopy As Range
      Set rngCopy = ActiveSheet.UsedRange
      rngCopy.Copy
      ActiveWorkbook.Close

      ' Paste to Original Book
      Windows(strBookName.Name).Activate 'activate original book
      Worksheets.Add(Before:=Worksheets(1)).Name = tmpBookName 'new sheet based on temp sheet name
      Cells(1, 1).Paste ' THIS IS WHERE ERROR IS OCCURING 

Answer 1

Avoid using select wherever possible. For instance, replace

ActiveSheet.UsedRange.Select
Selection.Copy

with

dim rngCopy as range
set rngCopy = activesheet.usedrange
    rngCopy.copy

'/ Go to other workbook

ActiveSheet.Cells(1,1).PasteSpecial xlPasteValues

even better, put all the data into an array (especially if you want to do anything with it) and then print the array to the second sheet

'/ Determine 1st/last row/column of the data
dim arrData as variant
    arrData = Array()
    ReDim arrData(bounds of the data range)
    arrData = RngCopy (from above)

new sheet:

set rngPaste = '/(Size of Array)
    rngpaste = arrData

Answer 2

For Using an array to store (perhaps operate on) and transfer the values:

Sub PasteUsingArray()

Dim rngCopy As Range
    rngCopy = ActiveSheet.UsedRange

    Dim LB1 As Long '/ Lower Bound of the 1st dimension (rows)
    Dim UB1 As Long
    Dim LB2 As Long
    Dim UB2 As Long

        LB1 = 1
        LB2 = 1 '/ Standard for arrays unless you specify otherwise

        UB1 = rngCopy.Rows.Count
        UB2 = rngCopy.Columns.Count '/ We now have the size of our data

        Dim arrData As Variant
            arrData = Array()

            ReDim arrData(LB1 To UB1, LB2 To UB2) '/ Now our array is the sime size as our data
            arrData = rngCopy '/ Voila, our array now contains all the data. What was in Cell(1,1) is now in arrData(1,1) [Asuming the data starts in cells(1,1)]

            '/ To print this data to a second sheet, we do this process in reverse.

        Dim rngPaste As Range

        Set rngPaste = Range(Cells(LB1, LB2), Cells(UB1, UB2))

        rngPaste = arrData

End Sub

You can either do stuff to the data before it's pasted, or afterwards, so long as the Array variable is still being referenced.

Answer 3

Combining a few things said here and what I found on the internet:

This final code will open a prompt to select a range of files, open each file, delimit based on the delimiter "|", save the values of the used ranges in each file into the original workbook as new worksheets, close the opened files. It is definitely not the most efficient, however it no longer uses select and activesheet.

Sub loopyarray()

Dim filenames As Variant

' get current workbook name to cut/paste opened sheets into
Dim strBookName As Workbook, tmpBookName As String
Set strBookName = ThisWorkbook
Dim str() As String

Dim myRow As Long
Dim myCol As Long

' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)

   counter = 1

   ' ubound determines how many items in the array
   While counter <= UBound(filenames)

      ' Opens the selected files
      Workbooks.OpenText filenames(counter), 437, 1, xlDelimited, xlTextQualifierDoubleQuote, 0, 0, 0, 0, 0, 1, "|"

      ' Copy From Temporary Book
      tmpBookName = ActiveSheet.Name ' save temporary sheet name
      Dim rngCopy As Range
      Set rngCopy = ActiveSheet.UsedRange
      Dim inputArray As Variant
      inputArray = rngCopy.Value ' convert used range to array
      ActiveWorkbook.Close

      ' Paste to Original Book
      Windows(strBookName.Name).Activate 'activate original book
      Worksheets.Add(Before:=Worksheets(1)).Name = tmpBookName 'new sheet based on temp sheet name
      For myCol = 1 To UBound(inputArray, 2)
        For myRow = 1 To UBound(inputArray, 1)
            Cells(myRow, myCol).Value = inputArray(myRow, myCol)
        Next
      Next

      ' increment counter
      counter = counter + 1

   Wend
End Sub