To write the data to different work book, name provided in the range "f"

[ajaar]

Hi Friends,

I wonder, is there any way to do this. I have data in A1:N1000, I wanted to filter by column 'F' and copy paste to new workbook already existing in 'c:\company'. for example in the attached file, I wanted to copy the data belong to user 'john' and paste in the workbook 'john', to copy the data belong to user 'saabi' and paste in the workbook 'saabi' and so on for all the user mentioned in the column "F".

appreciate your help.

Thanks
Ajaar

 

[Smallman]

Hi Ajaar

Give the following a try remembering to change the path.

Option Explicit

Sub SavetoWB()  'Excel VBA to export data
Const sPath = "C:\Test\"
Dim ar As Variant
Dim i As Integer
Dim owb As Workbook
Application.ScreenUpdating = False
Range("F1", Range("F" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , [Q1], True
ar = Range("Q2", Range("Q" & Rows.Count).End(xlUp))
'Loop through all unique instances of the Results from the Advanced Filter.
    For i = LBound(ar) To UBound(ar)
        Range("F1", Range("F" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
        Range("A1", Range("N" & Rows.Count).End(xlUp)).Copy 'Where Data is from Col A - N
        Set owb = Workbooks.Open(sPath & ar(i) & ".xlsx") 'Need to be non macro Enabled workbooks
        owb.Sheets(1).[A1].PasteSpecial xlPasteValues
        owb.Close True 'Close and Save
    Next i
   
Application.CutCopyMode = 0
[F1].AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

Take care

Smallman

 

[ajaar]

Dear Smallman,

Thank you so much for your time.
Code stops in the below line of code with error message "Run time error '9:' Subscript out of range. as you advised created macro free workbook '.xlsx'. could you please look into.

Set owb = Workbooks.Open(sPath & ar(i) & ".xlsx")

Thanks
Ajaar

 

[shrivallabha]

Ranges create Base 1 array so try changing error line from

Set owb = Workbooks.Open(sPath & ar(i) & ".xlsx")

to

Set owb = Workbooks.Open(sPath & ar(i, 1) & ".xlsx")

 

[ajaar]

Hi,

Great, thanks you so much Shrivallabha and Smallman.

Regards
ajaar