Results 1 to 10 of 10

Thread: Mirror master sheet.

  1. #1
    Acolyte chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016

    Mirror master sheet.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a master work sheet with thousands of records about 15 columns per record, one of the fields is a date column. I want have separate worksheets for each month based off the date field.

    I still want the running master sheet but would like to view the records separately by month in separate sheets… and any changes made to the master sheet continue to reflect on the monthly sheets
    This file is hosted on OneDrive so no macros

    Sent from my iPhone using Tapatalk
    Last edited by chrisl614; 2021-09-25 at 06:02 AM.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,712
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Being on OneDrive does not preclude VBA. It’s where the files are going to be opened that matters. In the browser version or mobile apps, VBA will not work, but in the desktop app it will.

    You could use PowerQuery to generate a table per month.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    The following focuses on Col A for the list of tabs to be created. It also copies all data located on the row an pastes that information to the correct tab. If the DATES in your workbook are not located in Col A, you can edit the macro
    to conform to your workbook layout.

    Code:
    Option ExplicitSub CreateSheets()
    
    
        Dim Cell    As Range
        Dim RngBeg  As Range
        Dim RngEnd  As Range
        Dim Wks     As Worksheet
    
    
            Set RngBeg = Worksheets("Sheet1").Range("A2")
            Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
    
    
            ' Exit if the list is empty.
            If RngEnd.Row < RngBeg.Row Then Exit Sub
    Application.ScreenUpdating = False
            For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
                On Error Resume Next
                    ' No error means the worksheet exists.
                    Set Wks = Worksheets(Format(Cell.Value, "[$-409]mmm;@"))
    
    
                    ' Add a new worksheet and name it.
                    If Err <> 0 Then
                        Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                        Wks.Name = Format(Cell.Value, "[$-409]mmm;@")
                    End If
                On Error GoTo 0
            Next Cell
    Application.ScreenUpdating = True
    
    
    MakeHeaders
    End Sub
    
    
    Sub MakeHeaders()
    Dim srcSheet As String
    Dim dst As Integer
    srcSheet = "Sheet1"
    Application.ScreenUpdating = False
    For dst = 1 To Sheets.Count
        If Sheets(dst).Name <> srcSheet Then
        Sheets(srcSheet).Rows("1:1").Copy
        Sheets(dst).Activate
        Sheets(dst).Range("A1").PasteSpecial xlPasteValues
        'ActiveSheet.PasteSpecial xlPasteValues
        Sheets(dst).Range("A1").Select
        End If
    Next
    Application.ScreenUpdating = True
    CopyData
    End Sub
    
    
    Sub CopyData()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    On Error Resume Next
    Lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Dim ans As String
    Dim ans2 As String
    
    
    NoVisi
    
    
        For i = 2 To Lastrow
        ans = Sheets("Sheet1").Cells(i, 1).Value
        ans2 = Format(ans, "[$-409]mmm;@")
            Sheets("Sheet1").Rows(i).Copy Sheets(ans2).Rows(Sheets(ans2).Cells(Rows.Count, "A").End(xlUp).Row + 1)
            Sheets(ans2).Columns("A:C").AutoFit
        Next
        
    
    
    Visi
    
    
    Application.ScreenUpdating = True
    
    
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A1").Select
    Exit Sub
    
    
    Application.ScreenUpdating = True
    
    
    End Sub
    
    
    Sub NoVisi()
    Dim CommandButton1 As Object
    
    
    CommandButton1.Visible = False
    
    
    End Sub
    
    
    Sub Visi()
    Dim CommandButton1 As Object
    
    
    CommandButton1.Visible = True
    End Sub
    Attached Files Attached Files

  4. #4
    Acolyte chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    Thank you all for your feedback.

    So I was looking online and found the following:

    1. Assume data on sheet1 is in range A5:B10. headings are in row 4
    2. Select A4:B10 and assign it a name, say dummy
    3. Select A4:B10 and press Ctrl+T to convert to a Table and save the file
    4. Click on any cell in sheet2 and go to Data > From Other Sources > From Microsoft Query > Excel Files
    5. Navigate to the folder where the file is saved, select the file and click on Next
    6. click on the plus sign in the left hand side box, select the column which you want on sheet2 and click on the > Symbol
    7. Click on Next twice
    8. Apply filters

    9. On the last screen, select "Return Data to MS Excel"
    10. In the Import Data box, select Table and click on Finish

    This did exactly what I wanted but the issue with this is, once the file leaves the location that its in, the refresh all button does not work. Is there any work around or similar option so I can move the file from computer to computer as well as uploading it to onedrive.
    Thank you!

  5. #5
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    I followed the instructions in your last post. The REFRESH selection works here.

    ?????

  6. #6
    Acolyte chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    Yes it works… but if you take that file and put it on a different computer it will not work because of the connection.


    Sent from my iPhone using Tapatalk

  7. #7
    Acolyte chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    I'm attached a copy of the file. Normally this file will have about 5k-15k records, I'm looking to mirror the "Master Sheet" and have separate worksheets. One sheet for every month.

    The query steps that I posted worked great. But this is a file that I will have posted on ONEDRIVE and multiple people will have access to it, and when I tried it on onedrive the worksheets stop updating because the connection is made on my pc. (I've never used query's before so this is pretty new to me)
    I tried creating worksheets based on formulas but the file ends up getting too big.

    Any help or suggestions would be appreciated.
    Attached Files Attached Files

  8. #8
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Please post a sample of the problem workbook (no confidential data). Let's see if it works here.

  9. #9
    Acolyte chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    Attached is the file. So everything works when I leave it saved on my desktop. But if I move the file to a different folder or to a different computer, when pressing "Refresh all" the month worksheets do not get updated.
    you can change the data on the master sheet and see that the other work sheets will not update.
    Attached Files Attached Files

  10. #10
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    I've researched this issue via Googling. Understand that this area of Excel VBA is all Greek to me. However hear are my thoughts I believe to be accurate.

    The workbook current settings reflect it is located on your desktop. When I try running the Refresh it tells me the path is incorrect :

    Click image for larger version. 

Name:	Path Wrong.jpg 
Views:	8 
Size:	65.9 KB 
ID:	10660

    Click image for larger version. 

Name:	Path.jpg 
Views:	7 
Size:	48.8 KB 
ID:	10661

    The file believes it is still located on your desktop. My research shows (I hope correctly) that you can dynamically edit this link with VBA macro code.
    Here is one reference that discusses this effort :

    https://analysistabs.com/excel-vba/a...ting-database/


    I believe you could use the ENVIRON command for the workbook path in the following macro provided by the above reference :

    Code:
    Sub sbADOExample()
    Dim sSQLQry As String
    Dim ReturnArray
    
    Dim Conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    
    Dim DBPath As String, sconnect As String
    
    
    DBPath = "C:\Users\" & Environ("username") & "\Desktop\test file.xlsx"
    
    
    
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
    
    'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
    'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    
    Conn.Open sconnect
        
        sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])
        
        mrs.Open sSQLSting, Conn
            '=>Load the Data into an array
            'ReturnArray = mrs.GetRows
                    ''OR''
            '=>Paste the data into a sheet
            Sheet2.Range("A2").CopyFromRecordset mrs
        'Close Recordset
        mrs.Close
    
    'Close Connection
    Conn.Close
    
    End Sub
    I believe if you edit the above macro as shown (red colored text), it will automatically reference the users workbook path for the connection.

    Understand there are other lines of code in the above macro that do not apply to your project. I regret you will have to work that out on your own (ODBC = Greek to me).


    To automatically run that above macro, you will need to put the following into the ThisWorkBook module :

    Code:
    Option Explicit
    
    
    Private Sub Workbook_Open()
        sbADOExample
    End Sub

    Other volunteers here can be of greater help to you than me.

    Best wishes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •