Sub ShowData()
Dim rng As Range, rng1 As Range
Dim wkbk As Workbook
Dim sh As Worksheet
With Worksheets("lw1")
Set rng = .Range("A1").CurrentRegion.Resize(, 11)
rng.AutoFilter Field:=3, Criteria1:="<"
End With
Set wkbk = Workbooks.Add(xlWBATWorksheet)
Set sh = wkbk.Worksheets(1)
rng.Copy sh.Range("A1")
sh.Range("D:F,I:I").EntireColumn.Delete
sh.Range("A1:G1").Value = Array("1st", "2nd", "3rd", "4th", _
"5th", "6th", "7th")
Set rng1 = sh.Range("A1").CurrentRegion
Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1)
Load UserForm1
UserForm1.ListBox1.ColumnCount = 7
UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.RowSource = rng1.Address(external:=True)
UserForm1.Show
wkbk.SaveAs FileName:="C:\Data\Myfile.csv", FileFormat:=xlCSV
wkbk.Close SaveChanges:=False
End Sub
You need to have a userform1 with a listbox named listbox1 and an OK button
that unloads the userform.
--
Regards,
Tom Ogilvy
lothario wrote in message
...
Hi,
I have a spreadsheet "lw1" with 25,000 rows and 21 columns.
Most of column C is usually blank.
But when column C is not blank, I would like to:
1. Grab columns A, B, C, G, H, J and K from all the rows
where column C is not blank.
2. Display these rows in a pop-up window.
3. With the column headings "1st", "2nd", "3rd", "4th",
"5th", "6th", "7th".
4. Add a (new) last column to pop-up window
called "calculated".
5. Where "calculated" is C+(C*G)+J in each row.
6. As soon as the user clicks on the "OK" button in the
pop-up window, I would like to save the contents
of the pop-up window into a csv file.
Can you give me the VBA code for this so that I can add it to a
button?
Thanks,
Luther
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/