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

Report
Posts
1
Registration date
Thursday January 25, 2018
Status
Member
Last seen
January 25, 2018
-
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
-
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

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
Always very elegant.

I wrote this to try to show others how these things work with each other. Let me know what you think:
https://ccm.net/faq/54862-how-to-copy-data-from-one-excel-sheet-to-another-using-a-formula
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
"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!