Mirror master sheet.

chrisl614

Member
Joined
Apr 6, 2019
Messages
30
Reaction score
0
Points
6
Excel Version(s)
2016
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:
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.
 
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
 

Attachments

  • New Sheets n Data From List.xlsm
    20.4 KB · Views: 5
Thank you all for your feedback.

So I was looking online and found the following:

[FONT=&quot]1. Assume data on sheet1 is in range A5:B10. headings are in row 4[/FONT]
[FONT=&quot]2. Select A4:B10 and assign it a name, say dummy[/FONT]
[FONT=&quot]3. Select A4:B10 and press Ctrl+T to convert to a Table and save the file[/FONT]
[FONT=&quot]4. Click on any cell in sheet2 and go to Data > From Other Sources > From Microsoft Query > Excel Files[/FONT]
[FONT=&quot]5. Navigate to the folder where the file is saved, select the file and click on Next[/FONT]
[FONT=&quot]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[/FONT]
[FONT=&quot]7. Click on Next twice
8. Apply filters[/FONT]

[FONT=&quot]9. On the last screen, select "Return Data to MS Excel"[/FONT]
[FONT=&quot]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![/FONT]
 
I followed the instructions in your last post. The REFRESH selection works here.

?????
 
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
 
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.
 

Attachments

  • file layout.xlsx
    17.2 KB · Views: 3
Please post a sample of the problem workbook (no confidential data). Let's see if it works here.
 
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.
 

Attachments

  • test file.xlsx
    25.7 KB · Views: 4
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 :

Path Wrong.jpg

Path.jpg

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/ado-sql-macros-connecting-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


[COLOR=#ff0000][SIZE=4][B]DBPath = "C:\Users\" & Environ("username") & "\Desktop\test file.xlsx"[/B][/SIZE][/COLOR]



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.
 
Back
Top