Excel VBA Macro: Autofilter, Copy, Paste to New Sheet & Save new file !! Please Help Family!

[highfly921]

According to the previous post attachment my revamped demonstration revised in order to allocate locally the pivot table source data,​

to paste to the Raw Data worksheet module :​

Sub Demo1r2d2()
         Dim V, R&
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
         Sheets(Array(Name, Sheet3.Name)).Copy
    With [A2].CurrentRegion
        .Columns(2).AdvancedFilter 2, , [K1], True
         V = [K1].CurrentRegion.Value2
     For R = 2 To UBound(V)
         [K2].Value2 = V(R, 1)
         ActiveSheet.UsedRange.Clear
        .AdvancedFilter 2, [K1:K2], ActiveSheet.[A1]
         ActiveSheet.Next.PivotTables(1).SourceData = ActiveSheet.[A1].CurrentRegion.Address(, , xlR1C1, True)
         ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Region " & V(R, 1), 51
     Next
    End With
         [K1].CurrentRegion.Clear
         ActiveWorkbook.Close
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

You should Like it !​

​

Thanks so much and appreciate your dedication and support here! This will go long way for me personally - Thank you!

 

[highfly921]

According to the previous post attachment my revamped demonstration revised in order to allocate locally the pivot table source data,​

to paste to the Raw Data worksheet module :​

Sub Demo1r2d2()
         Dim V, R&
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
         Sheets(Array(Name, Sheet3.Name)).Copy
    With [A2].CurrentRegion
        .Columns(2).AdvancedFilter 2, , [K1], True
         V = [K1].CurrentRegion.Value2
     For R = 2 To UBound(V)
         [K2].Value2 = V(R, 1)
         ActiveSheet.UsedRange.Clear
        .AdvancedFilter 2, [K1:K2], ActiveSheet.[A1]
         ActiveSheet.Next.PivotTables(1).SourceData = ActiveSheet.[A1].CurrentRegion.Address(, , xlR1C1, True)
         ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Region " & V(R, 1), 51
     Next
    End With
         [K1].CurrentRegion.Clear
         ActiveWorkbook.Close
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

You should Like it !​

​

Thanks! - very helpful - quick question - if I want to change the paste range from K1 to cell ZZ1 (or outside of the data range) - it seems like I would have to change the active range or something similar - could you please guide me what's the best way to change this macro? As I have over 200 columns and 100k rows worth of data so is there a way we can just keep the paste range outside of the active data range? Thanks again!

 

[Marc L]

Yes you can amend the VBA procedure just adding the Z character before each K1 and K2 cell reference as ZK is the #687 column …​

Another VBA way is either to work with the worksheet last column​

or from the worksheet UsedRange property choose a reference after the last column used.​

​