问题描述:

I have an excel file having a column C with sometimes repeteated values:

 A | B | C

------------

1 | 2 | a

1 | 4 | b

1 | 5 | c

1 | 2 | d

1 | 6 | a

4 | 2 | d

1 | 2 | a

4 | 4 | c

8 | 2 | c

1 | 8 | d

I would like create a macro that filter C column for each value (a,b,c,d, ..,n) and save the result in a new file.

In my example the macro return 4 file F1, F2, F3, F4 whit this content:

 A | B | C

------------

1 | 2 | a

1 | 6 | a

1 | 2 | a

A | B | C

------------

1 | 4 | b

A | B | C

------------

1 | 5 | c

4 | 4 | c

8 | 2 | c

A | B | C

------------

1 | 2 | d

4 | 2 | d

1 | 8 | d

How could be written the macro?

网友答案:

Here is the code. You have to manually save the created files everytime. Let me know if you want me to set a default location and save all files automatically.

\code\

Function perform_click()

Dim i, lastrow As Integer
Dim ws1, ws2 As Workbook

Set ws1 = ThisWorkbook
lastrow = ws1.Sheets("Sheet1").Range("C500000").End(xlUp).Row

ws1.Sheets("Sheet1").Range("C4:C" & lastrow).Copy Range("P4")
ws1.Sheets("Sheet1").Range("P4:P" & lastrow).RemoveDuplicates 1, xlNo


ActiveSheet.AutoFilterMode = False

For i = 4 To ws1.Sheets("Sheet1").Range("P500000").End(xlUp).Row

ws1.Sheets("Sheet1").Range("A3:C" & lastrow).AutoFilter 3, ws1.Sheets("Sheet1").Range("P" & i).Value
Set ws2 = Workbooks.Add
ws1.Sheets("Sheet1").Range("A3:C" & lastrow).SpecialCells(xlCellTypeVisible).Copy ws2.Sheets("Sheet1").Range("A3")

Application.DisplayAlerts = False
ws2.SaveAs "C:\Users\Praveen Behera\Desktop\F" & i & ".xlsx", 51
Application.DisplayAlerts = True

Set ws2 = Nothing
Next i

End Function

Private Sub CommandButton1_Click()

perform_click

End Sub
网友答案:

use a Oledb query to get the distinct values of column c into an array...and then write another query to get values for different values of the array.

相关阅读:
Top