Transposing data in column B as rows for 59 csvs using VBA

[paydog23]

I have 59 csvs in the same folder that contain field names in column B. A1 contains just the filename. The csvs need to be set up in a certain way to facilitate data migration to a state database. I would like to transpose the data in column B as rows (for example, instead of field names in B1:B5, I would like data from B1:F1). While the range size varies by csv (we have csvs with only 5 values while others have more than 30), the ranges are all contiguous. I can't even transpose a single csv--I was hoping to throw the following code into a Do While or For Next loop:

Sub TranposeData()
    Dim FieldNames As Range
    Dim lrow As Integer
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    Debug.Print lrow
    Set FieldNames = Range("B1:B" & lrow)
    FieldNames.Cut
    Sheets(1).Range("B1").PasteSpecial Transpose:=True
End Sub

I am defining the range as the FieldNames variable with lrow because the range size will vary with each csv--lrow allows me to exploit the contiguity of the data. Unfortunately, I encountered this error message:

.

 

[paydog23]

Book1.xlsm
	A	B
1	Book1	SDWIS
2		WQIR
3		EAR
4		DMU
5		WQI
6		SQL
Sheet1

 

[DanteAmor]

It is not allowed to cut and paste special - transpose.

Try this:

Sub TranposeData()
  Dim r As Range
  Set r = Range("B1", Range("B" & Rows.Count).End(3))
  Sheets(1).Range("B1").Resize(1, r.Rows.Count).Value = Application.Transpose(r.Value)
  r.Cells.Clear
End Sub

Or this:

Sub TranposeData()
  With Range("B1", Range("B" & Rows.Count).End(3))
    Sheets(1).Range("B1").Resize(1, .Rows.Count).Value = Application.Transpose(.Value)
   .Cells.Clear
  End With
End Sub

 

[paydog23]

Your code works except that it transposes the data into C1--I would like the data to be transposed into B1. Also, how would I reference the 59 different csv files using a loop?

 

[JLGWhiz]

If your host workbook is in the same directory, this would open all the csv files in the folder, do the copy and transpose column B, close and save the csv until all are done.
If the host workbook is not in the same directory, then the fPath line would need to be changed to reflect the correct directory.

Sub TranposeData()
Dim fPath As String, fName As String, wb As Workbook
Dim FieldNames As Range
fPath = ThisWorkbook.Path & "\" 'Assumes host workbook is in same directory.
fName = Dir(fPath & "*.csv")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            With wb.Sheets(1)
                Set FieldNames = .Range("B1", .Cells(Rows.Count, 2).End(xlUp))
                FieldNames.Copy
                .Range("C1").PasteSpecial xlPasteValues, Transpose:=True
                .Columns("B").Delete
            End With
            wb.Close True
        End If
        fName = Dir
    Loop
End Sub

 

[paydog23]

Holy cow, that worked! This was the last step in a long process! Thank you so much!

 

[JLGWhiz]

Holy cow, that worked! This was the last step in a long process! Thank you so much!

Glad we could help,
Regards, JLG