vba AutoFilter SpecialCells(xlCellTypeVisible) Copy - Only Values? Not Formulas?

[DocAElstein]

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"

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).

[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.

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”….

 

[RickXL]

Hi.

PasteSpecial seems to work:

Sub GrubSortinMrExcelAutoFilterCopyVisibleCells()
 
    Dim wks1 As Worksheet: Set wks1 = ThisWorkbook.Worksheets("Sheet6")
    Dim arrUnique() As Variant: Let arrUnique = Array("GrubA", "GrubB", "GrubC")
    Dim r As Long
    
    
    For r = 0 To 2 Step 1
        wks1.Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r)
        Let Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = arrUnique(r)
        wks1.Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy
        Worksheets(arrUnique(r)).Range("A1").PasteSpecial
        wks1.AutoFilterMode = False
    Next r
 
End Sub

 

[DocAElstein]

Hi.

PasteSpecial seems to work:
.............

...... You bet it does !
. I for ages last night with this one.. I probably had a Paste and a paste special or wotever tacked on the end of a Destination along the way.. etc. etc.. I tried all sorts of combinations .
. The tip from someone in the know is invaluable!

. I'm very grateful

. Thanks

. Alan Elston
.

 

[RickXL]

No problem.

I hope the headache has now gone away

Regards,

 

[DocAElstein]

Hi.
. I have now a solution from RickXL. (And my headache has gone mostly away, thanks again Rick!)
. To Rap the thread up I would still be interested in finding out why The three lines from me fail to copy the formulas. I have still have not been able to Google a satisfactory answer to this.

. So to summarize why does this line in red fail to copy formulas:

[color=darkblue]Sub[/color] Alan()
 
    [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=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
   
    [color=darkblue]For[/color] r = 0 [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] 1
        wks1.Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r)
        [color=darkblue]Let[/color] Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = arrUnique(r)
        [color=red]wks1.Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets(arrUnique(r)).Range("A1")[/color]
   
        wks1.AutoFilterMode = [color=darkblue]False[/color]
    [color=darkblue]Next[/color] r
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. Whereas these two lines in orange exceed in copying the formulas:

[color=darkblue]Sub[/color] RickXL()
 
    [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=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
   
   
    [color=darkblue]For[/color] r = 0 [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] 1
        wks1.Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r)
        [color=darkblue]Let[/color] Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = arrUnique(r)
        [color=orange]wks1.Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy
        Worksheets(arrUnique(r)).Range("A1").PasteSpecial[/color]
 
        wks1.AutoFilterMode = [color=darkblue]False[/color]
    [color=darkblue]Next[/color] r
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. In principle I am still believing they should be doing the same thing…. That is to say doing a simple paste of the Range held in the clipboard.
. Thanks again
Alan.

 

[RickXL]

Hi again.

I don't think you should worry too much about how Excel manages copy and paste. Down that road lies madness. I have spent many a happy hour trying to work out why one thing worked while another did not.

I do not know the answer to your question but I can shed some more light on how copy and paste works.

Copy with a Destination, I believe, does not use the clipboard but separate Copy and Paste or PasteSpecial statements do use the clipboard. They can use either the Windows clipboard of the Office clipboard. Windows is the default and only one item can be stored there at a time but if you select Home-->Clipboard then the Office one will be used and several things can be stored there at a time.

When you copy something to a clipboard you are not just saving one version of what you have copied. This is so that when copying from Excel, for instance, you could paste to either Excel, Notepad or Paint. The clipboard has a native copy for Excel, a text version for Notepad, a bitmap for Paint, an HTML version, an XML version etc etc. According to my recent tests, it seems as if Excel saves about 29 sets of data when you make a copy. When you then try and paste it looks through the list of formats on the clipboard and finds the most suitable one (I think this is where your paste is going wrong). I don't know exactly what rules it uses to find the most suitable one.

Whether you select just the visible cells or not seems to make a difference. Whether you paste to the current worksheet or another seems to make a difference. Sometimes even a PasteSpecial does not work and you have to force it to PasteSpecial Formulas. Guessing, when you say PasteSpecial Formulas it forces Excel to use a version of the clipboard data with the formulas available otherwise it just tries to make a best guess as to what is required.

Sorry I can't be any more help.

 

[DocAElstein]

Hi Rick,
. Many thanks for coming back on this one. (I had given mny follow up question as lost in the crazy speed that threads “vanish” down the line…”)
… and…….

…… I have spent many a happy hour trying to work out why one thing worked while another did not.

I do not know the answer to your question but I can shed some more light on how copy and paste works………..
.. ……………………. ……………………………………
………..
……………………Sorry I can't be any more help.

….. actually that explanation comes very close to clearing it up for me, if not totally satisfying my lust to understand what a code does and how and why before I use it myself or share it with others.
. Your explanation was very informative and ties up nicely with wot Rory A. discussed here from Post #3
http://www.mrexcel.com/forum/excel-questions/817446-range%3D-equivalent-range-value%3D-sometimes-range-range-value-anomaly.html?&&
.. ,as well as some Threads where Andrew Poulsom answered some Copy Destination:- Thread Questions (Unfortunately I cannot find them now just now).
. Putting it a lot less detailed than you (and thanks for that detail BTW, very much appreciated), it is coming back to the business of:

. 1) Excel guessing if you do not be too explicit

. 2) Some commands, such as the Copy Destination:= not allowing you to be too explicit – in the particular case of the Copy Destination:= that being a byproduct of the method used which you have explained uses a method where there may be limited options there to choose from

…. How one does these things will, I guess, be a personal choice and/ or based on RL factors such as speed both in the time that the code takes as well as how quickly the code can be written…

. I personally would like to be as explicit as possible. Therefore, based on wot I have learnt here from you I would choose always now to do it the following way….
. ……. – So a last version of that example code that I am happy with would be

[color=darkblue]Sub[/color] RickXLRickXL()
 
    [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=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
   
   
    [color=darkblue]For[/color] r = 0 [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] 1
        wks1.Range("A1:D10").AutoFilter Field:=3, Criteria1:=arrUnique(r)
        [color=darkblue]Let[/color] Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = arrUnique(r)
        [color=orange]wks1.Range("A1:D10").SpecialCells(xlCellTypeVisible).Copy
        Worksheets(arrUnique(r)).Range("A1").PasteSpecial [color=red]Paste:=xlPasteFormulas[/color] [/color]
 
        wks1.AutoFilterMode = [color=darkblue]False[/color]
    [color=darkblue]Next[/color] r
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

…………………………..

. If anyone else has any comments or further input I would be very grateful. But I think Rick you have satisfied that last nagging bit in the Buffer in my brain on this one so that I can go on and use this version in requirements using the “AutoFilter SpecialCells(xlCellTypeVisible) Copy” method. (At least until the next “anomaly” arises!!!!!!)

Many Thanks again Rick
Alan