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

Auto create workbook based on value in column?

Plutothedog

New Member
Hi,

i am trying to see if there is a way to auto create workbooks in excel based on the value in a particular column. for example i have a worksheet with 5500 records that are devided in 10 managers. each row has a name of the manager that aligns with it. I wanted to see if there is a way to automate creating of the worksheet based on the manager name.

Currently i have to filter by the manager copy that data and paste it in another tab manually. All of your help is greatly appreciated.

Thank you,
 
Plutothedog
Do You mean to create 10 workbooks and
copy each managers data to those new ones?
If so, then the answer is 'yes' -
but much 'easier' with VBA.
 
Yes that's exactly what i am looking for. I am not great with VBA.... if i provide the worksheet would you be able to put in the direction?
 
Do not provide your entire worksheet, but a representative sample. The code provided will work on your sample workbook and your actual workbook, if and only if the sample is a true representative of your actual.
 
Plutothedog - by the way -
Is that 'one time act' or
is it hourly/daily/weekly/monthly/yearly act?
If ... daily ...
then the most best way is to do it manually - Yourself!
 
Hi Pulto,

Please try this code will use ful.

Sub Splitdatatosheets()
' Splitdatatosheets Macro
Dim rng As Range
Dim rng1 As Range
Dim vrb As Boolean
Dim sht As Worksheet
'Range specfied to mention your manager names coloum
Set rng = Sheets("Sheet1").Range("A2")
'Range specfied to mention total coloums to moved bt sheet by sheet
Set rng1 = Sheets("Sheet1").Range("A2:cf2")
vrb = False
Do While rng <> ""
For Each sht In Worksheets
If sht.Name = Left(rng.Value, 31) Then
sht.Select
Range("A2").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
ActiveCell.Offset(1, 0).Activate
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
vrb = True
End If
Next sht
If vrb = False Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Left(rng.Value, 31)
Sheets("Sheet1").Range("A1:cf1").Copy ActiveSheet.Range("A1")
Range("A2").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
End If
vrb = False
Loop
End Sub
 
Hi Vletm,
I enclosed sample file of this working in coloum A "zone" it creat how many zones updated in Coloum A, and i have removed some data due to file size.

Thanks
Jawahar Prem
 

Attachments

  • samplee.xlsm
    170.8 KB · Views: 2
Back
Top