Create report code help

[sujittalukde]

I am using a file with some macros Now I am facing some problems in it. Can some one please help me to rectify it?

In the "Report Tools" sheet two option buttons are there one is for Company and other is for directors. I want to copy certain fields based on user selection to another WB which the macros are doiong. THe problems are now that-
1. For copying and pasting each field to the new WB, it is showing a popup message Which I want that it should not show.

2. All the selected fieds should be copied to new WB on one click of OK button.

3. In the new WB, Rows and colums should be automatically auto fit to size in width.

[Bob Phillips]

[vba]

Private Sub CommandButton2_Click()
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws1 As Excel.Worksheet

Set xlapp = New Excel.Application
xlapp.Visible = True

xlapp.ScreenUpdating = False

Set wb = xlapp.Workbooks.Add
Set ws1 = wb.Worksheets.Add
ws1.Name = "Company"
Dim allColum As Integer
Dim k As Integer
k = 1
For allColum = 1 To 20

If List.Selected(allColum) Then
Dim colName As String
colName = List.List(allColum)
Dim cellIndex As Integer
Dim rng As Range
Dim i As Integer
For i = 1 To 256
If ws.Cells(1, i) = colName Then
cellIndex = i
End If
Next i
Set rng = ws.Range(ws.Cells(1, cellIndex), ws.Cells(100, cellIndex))
rng.Copy
'ws1.Paste
Dim rngDest As Range
Application.DisplayAlerts = False
Set rngDest = ws1.Cells(1, k)
k = k + 1
rngDest.Select
ws1.Paste
Application.CutCopyMode = False
End If
ws1.Range("A1").Resize(, k).EntireColumn.AutoFit

Next allColum
Application.CutCopyMode = False

'wb.SaveAs "d:\report"

xlapp.ScreenUpdating = True

End Sub
[/vba]

[sujittalukde]

Thanks xld its working. Can you help me in one more front:
1. I want some filtering to the same file ie Now the output comes for all the companies if company option is selected Can a filtering option be added to select name of the companies so that output can be for selected companies say only A Ltd. or say A Ltd. or C Ltd.

2. For directors, suppose I want to know the name of the companies where Mr A or Mr B and so on is a director. So a filtering option based on directors name can be added.

[Bob Phillips]

Do you want to copy all of the data and then add a filter to the results, or restrict the cpying to that filetered value(s)?

[sujittalukde]

I want to copy the filetered value(s)?

[sujittalukde]

Bump!