Copy Rows From To CSV File

J

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top