Display selected rows and columns in a pop-up window.

[lothario]

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
button?

Thanks,
Luthe

 

[Tom Ogilvy]

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.

 

[lothario]

Thanks Tom