Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows From To CSV File
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows From To CSV File
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 Joe K. wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows to another file | Excel Worksheet Functions | |||
copy rows to another file | Excel Worksheet Functions | |||
copy rows to another file | Excel Worksheet Functions | |||
Copy rows from another file | Excel Discussion (Misc queries) | |||
Copy rows from one file to another file | Excel Programming |