• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help needed in creating multiple copies of workbooks in excel based on column value

Nitin Panjwani

New Member
I have attached a copy of data where I need multiple workbooks created based on column A named “Country Sales Office”
Based on the countries named in this column, the macro should create multiple workbooks with their respective names and the month name which will be dynamic (changing every month).
I have tried making a macro with some coding, but it screws up the data. I mean it gives data for some other country in some other country. (Some coding issue as it was built by a colleague who has now left the company)

If anyone could help, it would be helpful and valuable. Hope I am clear in explaining my problem. Feel free to reach out.
 

Attachments

PCI

Member
See next code
Code:
Option Explicit

Sub Treat()
Dim CtryDic   As Object
Set CtryDic = CreateObject("Scripting.Dictionary")
Dim Tmp
Dim I  As Long
Dim K
Dim WkPath As String, WkCtry As String
Dim WkRg  As Range

    WkPath = ActiveWorkbook.Path
    Set WkRg = Cells(1, 1).CurrentRegion
    
    Tmp = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
    For I = LBound(Tmp, 1) To UBound(Tmp, 1)
        CtryDic.Item(Tmp(I, 1)) = Empty
    Next I
    For Each K In CtryDic.keys
        If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False '  REMOVE  AUTOFILTER  IF  EXIST
        WkRg.AutoFilter Field:=1, Criteria1:=K
        
        Workbooks.Add
        WkRg.Copy Destination:=Cells(1, 1)
        ActiveWorkbook.SaveAs WkPath & "\" & K & "-" & Month(Now)
        ActiveWorkbook.Close
    Next K
End Sub
 
Top