Link Data [Solved/Closed]

Report
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010
-
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010
-
Dear Excel Expert,

I need help.

I need to manage my data with diffrent User different sheet & the data need to update every week in master sheet.

i know excel can do it (with formula or macro), but i no idea on it, so i looking for excel expert to help me.

My question is how to link the data from master sheet to user sheet (few user) with only 1 click like macro or formula?

example file as per link: https://authentification.site/files/22283627/ExampleY.xls

Thanks in advance

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
It will not take that long as you might be imagining

Sub MoveDataFromMasterToValueBasedSheet()

Dim sMasterSheet As String 'name of the master sheet
Dim sTempSheet As String 'name of a temp sheet for purpose of working
Dim iBasedOnColumn As Integer 'column number based on which sheets are to be generated
Dim lMaxUniqueValueCount As Long 'number of unique items on which sheets are to be generated
Dim lItemCount As Long ' unique items being counted
Dim sUniqueValue As String ' the value for which the sheet is being generated

    sMasterSheet = "Master"
    sTempSheet = "tempsheet"
    iBasedOnColumn = 1
    
    On Error Resume Next
        Sheets(sTempSheet).Delete
    On Error GoTo 0
    
    Sheets.Add
    ActiveSheet.Name = sTempSheet
    
    Sheets(sMasterSheet).Select
    
    If ActiveSheet.AutoFilterMode Then
        Cells.Select
        
        On Error Resume Next
        
        ActiveSheet.ShowAllData
        
        On Error GoTo 0
    
    End If
    
    Columns(iBasedOnColumn).Select
    Selection.Copy
    
    Sheets(sTempSheet).Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    If (Cells(1, 1) = "") Then
        LastRow = Cells(1, 1).End(xlDown).Row
        
        If LastRow <> Rows.Count Then
            Range("A1:A" & LastRow - 1).Select
            Selection.Delete Shift:=xlUp
        End If
    
    End If
    
    Columns("A:A").Select
    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True
    
    Columns("A:A").Delete
    
    Cells.Select
    Selection.Sort _
                Key1:=Range("A2"), Order1:=xlAscending, DataOption1:=xlSortNormal, _
                Header:=xlYes, OrderCustom:=1, _
                MatchCase:=False, Orientation:=xlTopToBottom
    
    lMaxUniqueValueCount = Cells(Rows.Count, 1).End(xlUp).Row
    
    For lItemCount = 2 To lMaxUniqueValueCount
    
        sUniqueValue = Sheets(sTempSheet).Range("A" & lItemCount)
        
        If sUniqueValue <> "" Then
        
            On Error Resume Next
                Sheets(sUniqueValue).Delete
            On Error GoTo 0
            
            Sheets.Add
            ActiveSheet.Name = sUniqueValue
            
            Sheets(sMasterSheet).Select
            Cells.Select
            
            If ActiveSheet.AutoFilterMode = False Then
                Selection.AutoFilter
            End If
            
            Selection.AutoFilter Field:=iBasedOnColumn, Criteria1:="=" & sUniqueValue, Operator:=xlAnd, Criteria2:="<>"
            
            LastRow = Cells(Rows.Count, iBasedOnColumn).End(xlUp).Row
            
            'in case you want the headers to be pasted also
            Rows("1:" & LastRow).Copy
            
            ' in case you dont want the headers to be pasted
            ' Rows("2:" & lastrow).Copy
            
            Sheets(sUniqueValue).Range("A1").PasteSpecial
        
        End If
    
    Next
    
    Sheets(sTempSheet).Delete
    
    Sheets(sMasterSheet).Select
    If ActiveSheet.AutoFilterMode Then
        Cells.Select
        ActiveSheet.ShowAllData
    End If

End Sub

1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

Ok...will check my comp system.

Million thank for your help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Are you looking to copy the data from your Master sheet to other sheets ? If so are you looking to append to these sheets or each time old data gets wiped out and data from master is moved to these sheets.

If you are looking for formula based links, a seemingly blank row is acceptable to you. By that i mean if row 2 on master sheet is for AP, then on CP sheet, row 2 look blank
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

for me redo it is take time... data is up to 40k plus (A to AB).

that why i prefer to link the data. (updating master list & keep the user list update).
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

Million thanks.

Nope... i know u can give a help in sort time.

now i study on it.

Thank again.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
just make sure that you delete the blank row from row 1 (as was in your sample book)
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

error msg pop up: System error & H80010108. The object invoked has disconected from its clients.

you know how to solve it?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
No error here. Try to step thru code to see what is the issue. MS folks have this to say about the error https://support.microsoft.com/en-us/help/319832

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!