Hi venky24,
I am also new to VBA and in the very initial phase of learning. However, I have tried to answer your question as follows:
I have taken the data from the link (http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/) you have mentioned above.
There are four salesmen in that data-Joseph, Maria, Matt and Lawrence.Entirre data ranges from A1 to G1080.
I would request you to do the following:
i)Create an empty workbook and name it as "Region"(as you preferred) and close it.
ii)Place the four salesmen from L2 to L5 in the workbook ("break-data-example.xlsm") that actually contain the data
iii) In this workbook: press alt+f11 to open Visual Basic Editor.Insert a module and copy paste the following code
Sub CreateNewData()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim lstrow As Long
Dim i As Integer
Dim j As Integer
Dim tempname As String
Dim curWorkbook As Workbook
Dim wb1 As Workbook
i = 2
j = 1
lstrow = ThisWorkbook.Worksheets("Sheet1").Range("L" & Rows.Count).End(xlUp).Row
Do While Range("L" & (i)).Value <> ""
tempname = Range("L" & (i)).Value
Set wb1 = Workbooks.Open("C:Documents and Settingskaushik DuttaDesktopRegion.xlsx").....''remember to change the path in this line of code where you have saved your workbook(Region)''
Worksheets.Add
Workbooks("break-data-example.xlsm").Activate
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$G$1080").AutoFilter Field:=2, Criteria1:=tempname
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wb1.Activate
Worksheets("Sheet" & (j)).Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet" & (j)).Name = tempname
wb1.Save
i = i + 1
j = j + 1
Workbooks("break-data-example.xlsm").Activate
Sheets("Sheet1").Select
Loop
wb1.Activate
Dim sh As Variant
On Error GoTo Exits:
For Each sh In Sheets
If Not IsChart(sh) Then
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
End If
Next sh
Exits:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Public Function IsChart(sh) As Boolean
Dim tmpChart As Chart
On Error Resume Next
Set tmpChart = Charts(sh.Name)
IsChart = IIf(tmpChart Is Nothing, False, True)
End Function
It works for.
Let me know if you face any challenge.
Regards,
Kaushik
