0

I have a folder containing nearly 1,000 .csv files. I would like to grab the second column from each of these files and transpose-paste them into a new Excel workbook, so that the data is in one row. The following is what i have so far:

Sub OpenFiles2()

  Dim MyFolder As String
  Dim MyFile As String

  'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

  With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
  End With

  'In Case of Cancel
  NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

  ResetSettings:
  'Reset Macro Optimization Settings
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

  Do While myPath <> ""
    Range(Range("B1"), Range("B1").End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWorkbook.Close True
    Windows("Compiled.xlsm").Activate
    Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial Transpose:=True

    MyFile = Dir
  Loop
End Sub

For some reason I keep getting an error for the Paste Special command. I also tried to replace it with:

ActiveSheet.PasteSpecial Transpose:=True

And

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=      False, Transpose:=True

Still got errors. Please help. Thank you.

Community
  • 1
  • 1
user6272309
  • 29
  • 1
  • 2
  • 1
    `.Range("A" & Rows.Count)` guessing the `rows.count` is faulty since you didn't specify which sheet the rows are on. – findwindow Apr 29 '16 at 16:56
  • 1
    *I keep getting an error* and *Still got errors* are absolutely meaningless as a problem description unless you include information about what **specific errors** you're getting.. You have that information on your screen, right in front of your eyes. There is zero excuse to not provide it to us as well when you're asking us to donate **our time** to solve **your problem**. – Ken White Apr 29 '16 at 17:36
  • 1
    @findwindow - no need to guess! In addition to specifying the sheet, I ***highly*** recommend reading through [how to avoid using `.select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Apr 29 '16 at 18:12
  • 1
    Always listen to Batman. – findwindow Apr 29 '16 at 18:20
  • 1
    This probably isn't your issue, but I'll mention Excel's Transpose function silently drops any rows over 32k from the transposed result. – Snachmo Apr 29 '16 at 18:21

1 Answers1

0

I would avoid using select and deal with the values. This code stores the original value in a variable, then you can close the active workbook and use the data in that variable by using the Application.Transpose within VBA.

Replace the Do Loop with the below code. 

Do While myPath <> ""
    lastrow = Cells(Rows.Count, 2).End(xlUp).Row
    x = Range("B1:B" & lastrow).Value
    ActiveWorkbook.Close True
    With Worksheets("Sheet1")
        Range("A" & .Cells(.Rows.Count, 1).End(xlUp).Row + 1). _
        Resize(, lastrow).Value = Application.Transpose(x)
    End With
    MyFile = Dir
Loop