DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
vba AutoFilter SpecialCells(xlCellTypeVisible) Copy - Only Values? Not Formulas?
Hi,
. I have not been able to Google an answer to this one. Can anyone help. The problem follows on from this Thread (Starting from Post #32):
http://www.mrexcel.com/forum/excel-...le-worksheets-based-column-4.html#post4039255
. The basic problem is that mostly only values rather than formulas seem to be copied when using the typical “AutoFilter copying visibly Filtered Cells” Techniques for Spliting data into multiple worksheets based a search Criteria in one column.
(. I was able to “Bodge” a solution. This involved Re – Copying the data once the Filtered ranges sizes info was obtained by the initial copy from the initial Auto Filter attempt.)
. I wish to know if it is possible to copy the formulas through some simple modification (different arguments etc.) using the AutoFilter or similar method, rather than resorting back to a more basic complicated“Looping and Comparing for Match” type solution.
. To demonstrate the problem I have organized a simple example.
. Here I have a table to be Split into 3 sheets based on the Grub Grouping: "GrubA", "GrubB", or "GrubC"
. In the classical AutoFilter Method I would use 3 main Code lines in 3 cycles of a Loop based on the Filter “Grub” Criteria, "GrubA", "GrubB", and "GrubC"
.
>> Cycle 3 times:-
.1) Making “visible” only data records with the “Grub” group in column 3
.2) Making a new sheet with the “Grub” group name
.3) Copying the “visible” Filtered data records to the new sheet.
. Here below I have written a very simple program which demonstrates and achieves this. (But unfortunately only copies values and not the formulas).
Code again, shimpglified without comments etc.
. Can anyone enlighten me on a similar code which will also copy formulas, or tell me the “missing” bit to make my code above copy formulas.
Thanks
Alan.
P.s. I have experimented with this extensively, and only one interesting result to date was that if the first Grub groups are all at the start of the table such as this:
… then the formulas ARE copied but only to that first sheet, Sheet “GrubA”….
Hi,
. I have not been able to Google an answer to this one. Can anyone help. The problem follows on from this Thread (Starting from Post #32):
http://www.mrexcel.com/forum/excel-...le-worksheets-based-column-4.html#post4039255
. The basic problem is that mostly only values rather than formulas seem to be copied when using the typical “AutoFilter copying visibly Filtered Cells” Techniques for Spliting data into multiple worksheets based a search Criteria in one column.
(. I was able to “Bodge” a solution. This involved Re – Copying the data once the Filtered ranges sizes info was obtained by the initial copy from the initial Auto Filter attempt.)
. I wish to know if it is possible to copy the formulas through some simple modification (different arguments etc.) using the AutoFilter or similar method, rather than resorting back to a more basic complicated“Looping and Comparing for Match” type solution.
. To demonstrate the problem I have organized a simple example.
. Here I have a table to be Split into 3 sheets based on the Grub Grouping: "GrubA", "GrubB", or "GrubC"
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ColumnA | ColumnB | Grouping | Total =ColumnA+ColumnB | ||
2 | 3 | 6 | GrubB | 9 | ||
3 | 2 | 4 | GrubA | 6 | ||
4 | 1 | 1 | GrubB | 2 | ||
5 | 3 | 5 | GrubC | 8 | ||
6 | 2 | 10 | GrubA | 12 | ||
7 | 3 | 4 | GrubC | 7 | ||
8 | 1 | 1 | GrubC | 2 | ||
9 | 2 | 7 | GrubC | 9 | ||
10 | 2 | 4 | GrubA | 6 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | =A2+B2 | |
D3 | =A3+B3 | |
D4 | =A4+B4 | |
D5 | =A5+B5 | |
D6 | =A6+B6 | |
D7 | =A7+B7 | |
D8 | =A8+B8 | |
D9 | =A9+B9 | |
D10 | =A10+B10 |
. In the classical AutoFilter Method I would use 3 main Code lines in 3 cycles of a Loop based on the Filter “Grub” Criteria, "GrubA", "GrubB", and "GrubC"
.
>> Cycle 3 times:-
.1) Making “visible” only data records with the “Grub” group in column 3
.2) Making a new sheet with the “Grub” group name
.3) Copying the “visible” Filtered data records to the new sheet.
. Here below I have written a very simple program which demonstrates and achieves this. (But unfortunately only copies values and not the formulas).
Code:
[color=darkblue]Sub[/color] GrubSortinMrExcelAutoFilterCopyVisibleCells()
[color=darkblue]Dim[/color] wks1 [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wks1 = ThisWorkbook.Worksheets("Sheet1")
[color=darkblue]Dim[/color] arrUnique() [color=darkblue]As[/color] Variant: [color=darkblue]Let[/color] arrUnique = Array("GrubA", "GrubB", "GrubC") [color=green]'Criteria for Filtering (Grub Names). Variant must be used as Array Method is first "seen" by arrUnique[/color]
[color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Row number in main Sheet[/color]
[color=darkblue]For[/color] r = 0 [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] 1 [color=green]'Go through Filter Criteria 3 times.[/color]
wks1.Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r) [color=green]'This results in only Records with critical value in column 3 being seen in main sheet[/color]
[color=darkblue]Let[/color] Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = arrUnique(r) [color=green]'This Results in a new sheet being made after the last sheet with the name of the Filter Criteria[/color]
wks1.Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets(arrUnique(r)).Range("A1") [color=green]'This Results in Visible (Filtered) Records from main sheet being copied to the new sheet[/color]
wks1.AutoFilterMode = [color=darkblue]False[/color] [color=green]'This makes everything visible again in main sheet. - (Usually done once at the end, but useful for debuggung purposes to do within loop)[/color]
[color=darkblue]Next[/color] r
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'GrubSortinMrExcelAutoFilterCopyVisibleCells()[/color]
Code again, shimpglified without comments etc.
Code:
Sub GrubSortinMrExcelAutoFilterCopyVisibleCellsSHimGlified()
arrUnique = Array("GrubA", "GrubB", "GrubC")
For r = 0 To 2
Sheets.Item(1).Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = arrUnique(r)
Sheets.Item(1).Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets(arrUnique(r)).Range("A1")
Next r
End Sub
. Can anyone enlighten me on a similar code which will also copy formulas, or tell me the “missing” bit to make my code above copy formulas.
Thanks
Alan.
P.s. I have experimented with this extensively, and only one interesting result to date was that if the first Grub groups are all at the start of the table such as this:
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ColumnA | ColumnB | Grouping | Total =ColumnA+ColumnB | ||
2 | 3 | 6 | GrubA | 9 | ||
3 | 2 | 4 | GrubA | 6 | ||
4 | 1 | 1 | GrubB | 2 | ||
5 | 3 | 5 | GrubB | 8 | ||
6 | 2 | 10 | GrubC | 12 | ||
7 | 3 | 4 | GrubB | 7 | ||
8 | 1 | 1 | GrubC | 2 | ||
9 | 2 | 7 | GrubC | 9 | ||
10 | 2 | 4 | GrubC | 6 | ||
Sheet1 |
… then the formulas ARE copied but only to that first sheet, Sheet “GrubA”….