Copy Rows From To CSV File

[Joe K.]

I have a spreadsheet with the current worksheet that has data in the format
listed below. The dates are in row1 and the state abbreviation are listed in
Column A.
The values for each states are listed on each row.

Please help me create a macro that will take the worksheet values and output
to a
CSV file in the desired output listed below.

Thank you very much.



Spreadsheet
5/26/07 5/27/07 5/28/07 ... 08/30/07
NM 56.0 68.0 72.0 64.0
TX 23.0 28.0 34.0 16.0
NY 16.0 18.0 22.0 32.0

Desired Output

5/26/07,NM,56.0
5/27/07,NM,68.0
5/28/07,NM,72.0
....
8/30/07,NM,64.0
5/26/07,TX,23.0
5/27/07,TX,28.0
5/28/07,TX,24.0
....
8/30/07,TX,16.0
5/26/07,NY,16.0
5/27/07,NY,18.0
5/28/07,NY,22.0
....
08/30/07,NY,32.0

 

[Dave Peterson]

One way:

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myStr As String

Set wks = Worksheets("sheet1")

Close #1 'just in case it's open
Open "C:\Temp\Output.CSV" For Output As #1

With wks
FirstRow = 1
FirstCol = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iRow = FirstRow + 1 To LastRow
For iCol = FirstCol + 1 To LastCol
myStr = .Cells(1, iCol).Text & "," _
& .Cells(iRow, "A").Text & "," _
& .Cells(iRow, iCol).Text
Print #1, myStr
Next iCol
Next iRow
End With

Close #1
End Sub