i am maintaining a master excel sheet of out tele callers in which they all enter their calling data.
i want to get the data in different different sheet by name of a caller. how do i get that automatically
??
for example i did a entry in master sheet like cust name, number, address, product type, remarks ETC and that i get all the data automatically in sheet 2. same as my other telecaller has done a entry in the same sheet and that will automatically update in her/his sheet name whichever it will, sheet 2 sheet 3 sheet 4.
I'm not sure how your main input sheet is set up but a code as follows may be a good starting point for you:-
Sub CreateNewShtsTransferData()
Dim sht As Worksheet
Dim lr As Long, x As Long
Dim ID As Object
Dim key As Variant
Set sht = Sheet1
Set ID = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lr = sht.Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To lr
If Not ID.Exists(sht.Range("A" & x).Value) Then
ID.Add sht.Range("A" & x).Value, 1
End If
Next x
For Each key In ID.keys
If Not Evaluate("ISREF('" & key & "'!A1)") Then
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
End If
sht.Range("A1:A" & lr).AutoFilter 1, key
sht.[A1].CurrentRegion.Copy Sheets(key).[A1]
Sheets(key).Columns.AutoFit
sht.[A1].AutoFilter
Next key
sht.Select
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation
End Sub
The code takes each telecaller's name in Column A, creates a sheet for each individual telecaller and then transfers the relevant rows of data to each individual's sheet..
Following is the link to a sample workbook that I have prepared for you:-
Thanks Mark. I've yet to hear back from the OP so I don't know if its suitable or even works for her.
I just had a look at the How To. Very nice, very informative.
I just hope that people take advantage of the How To section even if its just to make a start on the road to understanding the processes.
- Posts
- 13028
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- September 20, 2019
1401 -I wrote this to try to show others how these things work with each other. Let me know what you think:
http://ccm.net/faq/54862-how-to-copy-data-from-one-excel-sheet-to-another-utilizing-a-userform
- Posts
- 1248
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 17, 2019
205 -I just had a look at the How To. Very nice, very informative.
I just hope that people take advantage of the How To section even if its just to make a start on the road to understanding the processes.
Cheerio,
vcoolio.
- Posts
- 13028
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- September 20, 2019
1401 -That was the goal. Refer them to that first. Then help when they are stuck!