Transfer data from the master sheet to other sheets. [Closed]

Posts
1
Registration date
Thursday January 25, 2018
Status
Member
Last seen
January 25, 2018
-
Hello,

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.

Thanks
Teena Jain
See more 

1 reply

Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
Hello Teena,

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:-

http://ge.tt/8xJ2tGo2

Click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.
ac3mark
Posts
13028
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
1401 -
Always very elegant.

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
vcoolio
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205 -
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.

Cheerio,
vcoolio.
ac3mark
Posts
13028
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
1401 -
"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."

That was the goal. Refer them to that first. Then help when they are stuck!