Link Data

Solved/Closed
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010
- May 5, 2010 at 09:19 AM
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010
- May 5, 2010 at 11:34 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2010 at 10:36 AM
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
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

May 5, 2010 at 11:34 AM
Ok...will check my comp system.

Million thank for your help.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2010 at 09:24 AM
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
0
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

May 5, 2010 at 09:31 AM
Dear Rizvisa1,

i need maintain master sheet, and in the same time i want to keep the user data in user sheet like (AP, CP).

blank row is wasting my raw. my database can go up to 40k plus.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2010 at 09:41 AM
So again. Are you looking to copy data from master to other sheets. When you copy like that you have two ways to go

1. The older data from other sheets get deleted and new data from master is copied

2. the older data is left alone and new data is added from master to these sheet

you can have one of those two. how you are going to do it from one of the two possibilities
0
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

May 5, 2010 at 09:52 AM
option 2 is my prefer
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2010 at 10:02 AM
and how would one say on master sheet that from a particular row down, this is the new data and should be copied. While and any thing above that row is older should not be copied as it was copied before?
0
YeohSeow
Posts
17
Registration date
Sunday May 2, 2010
Status
Member
Last seen
May 25, 2010

May 5, 2010 at 10:05 AM
not only copy new data from master sheet, but also need to update the old data time to time to user sheet

Thank for your patient.
0