Automate date range entry

[mikecox39]

I'm the treasurer of my HOA. I created a budget spreadsheet that works well but I will be giving up the position a some point so I want to create a Dashboard for the person who takes over for me, to make it "user friendly".

Every year I have to start with a new sheet, changing the dates, removing data, etc. Since the fiscal year starts next month I decided to work on automating the process of setting up the new spread sheet.

see the attached link for details.


https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

 

[mikecox39]

I have no idea what d.docs is and doubt the request is coming from Excel as nothing I worked with has any password.

Sorry to bother with this, thought maybe there was a simple answer on our end.

A Google search for d.docs indicates it's something to do with Microsoft's OneDrive, which I don't use so
sorry, I'm afraid I can't help with that.

Sorry to bother you with this, just thought there might be a simple answer on your side.

I too did a Google search and came up with what you did. OneDrive is part of MS Live and I have a MS Live account that uses OneDrive all the time, it's just cloud memory, but I've never been prompted for another PW before

I did a "Live Chat" with MS today and asked about why I'm being prompted for another PW but level one support didn't have the answer so they escalated the issue and gave me a link to level two support.

I was late for the gym so I left as soon as they finished.

But here's the kicker!!

When I got home I opened the workbook again and studied the macro. Then I noticed the "allow macros" warning at the top of the page! I clicked the "allow" option and went to the budget page. The error messages were gone and the cells were populated with the data from last year's Master sheet!

So, it looks like I can dismiss that PW prompt that comes up when the macro runs, because it isn't needed. The macro works perfectly just the way it is! I just have to allow macros when I open the workbook.

I just wish I could figure out a way to avoid that prompt so whoever takes over for me doesn't have to deal with it. Maybe I'll find out tomorrow from level two support.

 

[mikecox39]

But here's the rub.

The macro cleared the data in the current Master sheet: MB18.

Here is MB18 after the macro ran and the copy I saved.

https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

fyi I changed the ranges in the C column to preserve that static data in that column

 

[NoSparks]

On my computer I get asked to asked to Enable Content on Excel .xlsm files until I do, then not again for that file from that location. Move the file to another folder and I get asked again.

You might also want to look into Excel's Trust Center.
File > Options > Trust Center > Trust Center Settings...

But here's the rub.

The macro cleared the data in the current Master sheet: MB18.

Here is MB18 after the macro ran and the copy I saved.

https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

fyi I changed the ranges in the C column to preserve that static data in that column

Those two files are not the same. The copy has only one macro in it.
"MB18" has two macros in it. Want me to guess which macro you ran?

 

[mikecox39]

On my computer I get asked to asked to Enable Content on Excel .xlsm files until I do, then not again for that file from that location. Move the file to another folder and I get asked again.

You might also want to look into Excel's Trust Center.
File > Options > Trust Center > Trust Center Settings...

Those two files are not the same. The copy has only one macro in it.
"MB18" has two macros in it. Want me to guess which macro you ran?

Ok, I was up til 2am last night with this and was kind of brain dead when I finished. I've had a good nights sleet and have been trying all kinds of things to sort this out, and to not bother you any more but...

Things are gotten so confusing.

First I did look into the Trust Center but didn't see any options that would stop the "enable macros" option from poping up.

I tried putting MB18 on my C: drive, separate from OneDrive. fyi OneDrive is configured to merge MS's system Documents folder, on C:, with the Documents folder on OneDrive, so I put MB18 in a folder outside the Documents folders. But I got error messages.

I noticed that MB19, created by the macro was missing formulas in the Budget sheet the last time I checked, where there were formulas before (they are back, see my last screenshot).

I think saved versions of MB18 have gotten corrupted somehow, which may be why things have gotten so confusing. As I've said, I've really tried to sort it out without bothering you any more than possible.

btw I am aware that the macro saved a copy of MB18, but I cant find a saved MB18 file where the data hasn't been removed.

https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

The file named "Last used Copy MB18" is probably not corrupted and is the file I ran the macro on. It created the screenshot named "Ran just before posting". I don't know what created the "no formulas" result but it's moot since they are back in "last used...".

Again, I truly apologize for all this! I will be contacting MS about this d.docs issue as soon as I post this.

 

[NoSparks]

No need to apologize, we've all been there at one time or another.
I suspect one cause of confusion is numerous files named "MB18" in numerous folders because you couldn't test anything ahead of time from the 'real' location without moving or renaming other files.

Here's what I would do: (edit: or would have done)
copy "Copy MB18.xlsm" to the folder you want to use,
rename it to "MB18.xlsm"
Run the macro, there's only one in the workbook... Sub Setup_for_NewYear()

Now the MB18 and MB19 must remain in this folder for the linking formulas to work.
If you should move them, you'll need to update the path to MB18 in the formulas of MB19.

Hope that helps.

 

[mikecox39]

No need to apologize, we've all been there at one time or another.
I suspect one cause of confusion is numerous files named "MB18" in numerous folders because you couldn't test anything ahead of time from the 'real' location without moving or renaming other files.

Here's what I would do: (edit: or would have done)
copy "Copy MB18.xlsm" to the folder you want to use,
rename it to "MB18.xlsm"
Run the macro, there's only one in the workbook... Sub Setup_for_NewYear()

Now the MB18 and MB19 must remain in this folder for the linking formulas to work.
If you should move them, you'll need to update the path to MB18 in the formulas of MB19.
ope that helps.

From the beginning I have made a point of separating all MB18 files into a separate folder and as an extra precaution appending numbers to their name, all in an effort avoid confusing the macro.

I just moved the file with the most current data to the folder I have always kept the MB files in and removed the appended numbers I'd put in the name.

I then opened the MB18 file, cleared the Security warning, opened the editor, found the macro, and pressed F5.

I got the prompts generated by the macro, and watched the data clear from the Master sheet. Then came the "Connect to d.decs.live.net" window, prompting for a password.

I entered my MS.live PW and, this time, it worked! But nothing seemed to be happening after the window closed, so after a minute or so I closed the workbook and checked the folder. There were two files in the folder MB18 and MB19. The former was empty, the latter was as well, including the Budget sheet whose cells only contained $0.00. So no data was imported.

The formula in the address field was as shown
='https://d.docs.live.net/585de67c78938385/Documents/Office

when I clicked on it it displayed
OD/Condo/[MB18.xlsm]Master'!T23 with T23 in the cell.

I know that is the address of the data in the MB18 Master sheet, which should have copied over

So what happened? Did the data get copied after it was deleted? Which would account for the zero amounts. Also, why did the data get cleared from the saved copy of MB18?

I wondered if it was possible that the Security protection was interfering with the process so I set "Macro Settings to the least (not recommended) protection to "Enable all macros", and tried again, but it didn't help. Oddly I actually found the Security warning on the MB19 sheet, in spite of turning it off in MB18. I also tried F9 in the Budget sheet.

 

[NoSparks]

It's really tough to trouble-shoot something like this when on my computer it just works.
I don't have that d.doc stuff so am suspect of that.
My gut feeling is that it's a timing issue and the macro is carrying on before the system first saves MB19,
effectively keeping the macro actions in MB18, but I really don't know.


I'd be curious to know what happens if instead of using F5 to run the macro you use F8.
The editor stays on screen and only executes the one line of code that is highlighted when you press the key.
It also creates a long time delay between things which might make a difference.

 

[mikecox39]

It's really tough to trouble-shoot something like this when on my computer it just works.
I don't have that d.doc stuff so am suspect of that.
My gut feeling is that it's a timing issue and the macro is carrying on before the system first saves MB19,
effectively keeping the macro actions in MB18, but I really don't know.


I'd be curious to know what happens if instead of using F5 to run the macro you use F8.
The editor stays on screen and only executes the one line of code that is highlighted when you press the key.
It also creates a long time delay between things which might make a difference.

I actually wondered about that speed thing so did as you suggested last night. That wasn't it though.


What's happening is the current workbook is not being archived before the macro clears it's data so the record gets destroyed. The other problem is that when the macro gets to the Budget sheet there is no data available because the Master sheet has already been cleared.


I think the first thing that should happen is for the macro to create an archive of the current workbook by changing ActiveWorkbook.Save to ActiveWorkbook.SaveAs.


I'm just learning about offests and I was thinking an offset could be used to put a space between the MB and the 18 in the new name. That way all renamed workbooks would be changed from CCnn to CC nn.


The next thing would be to clear the data in the Budget sheet, then pull in the data from the Master sheet and convert it to hard data because, as I understand it, numbers created by a formula would be lost if the data referenced by the formula was removed. Finally open the Master sheet and clear the data then do the same to the details sheet.

The only thing that would be left is the skeleton of MB18, which I guess would have to removed manually?


Does that make sense?

 

[mikecox39]

I actually wondered about that speed thing so did as you suggested last night. That wasn't it though.


What's happening is the current workbook is not being archived before the macro clears it's data so the record gets destroyed. The other problem is that when the macro gets to the Budget sheet there is no data available because the Master sheet has already been cleared.


I think the first thing that should happen is for the macro to create an archive of the current workbook by changing ActiveWorkbook.Save to ActiveWorkbook.SaveAs.


I'm just learning about offests and I was thinking an offset could be used to put a space between the MB and the 18 in the new name. That way all renamed workbooks would be changed from CCnn to CC nn.


The next thing would be to clear the data in the Budget sheet, then pull in the data from the Master sheet and convert it to hard data because, as I understand it, numbers created by a formula would be lost if the data referenced by the formula was removed. Finally open the Master sheet and clear the data then do the same to the details sheet.

The only thing that would be left is the skeleton of MB18, which I guess would have to removed manually?


Does that make sense?

I'm sending this copy of the macro that isn't working for me because I can't imagine it's the one you are using and if it is I can't understand how it would work for you, since the Budget sheet part of the code come after the Master sheet had been cleared of data that is supposed to be in the Master sheet, and because the current workbook isn't being saved so the workbook is wiped out in the process. So I have included it here, with my own comments.

Sub Setup_for_NewYear()

    Dim rng As Range, cel As Range, str As String
    Dim yr As Long, tbl As ListObject


Sheets("Master").Select


'-> Protect the current workbook by adding a Save current workbook line


    ActiveWorkbook.Save


'->Add: Note: current MB workbook has been saved,
'------->but it's not protected, it's data ends up being cleared by the macro
'-> Now a new, blank, workbook will be create for next year named MBnn (nn=current year)
    MsgBox "The current workbook has been saved" & vbLf & _
           "A new, blank workbook will be created for next year" & vbLf & _
           "It will be named  MB" & Right(Sheets("Master").Range("C1"), 2) + 1


'change dates and clear Master sheet
With Sheets("Master")
    'Dates
    For Each cel In .Range("C1:N1")
        cel.Value = DateAdd("yyyy", 1, cel.Value)
    Next cel
    'Clear data
    .Range("C3:N11").ClearContents
    .Range("C13:N14").ClearContents
    .Range("C35:N40").ClearContents
    .Range("C3:N40").ClearComments
    'Change quarters year
    .Range("P1").Value = "1st  quarter " & Year(.Range("C1").Value)
    .Range("Q1").Value = "2nd quarter " & Year(.Range("C1").Value)
    .Range("R1").Value = "3nd quarter " & Year(.Range("N1").Value)
    .Range("S1").Value = "4th   quarter  " & Year(.Range("N1").Value)
End With

'------->at this point there is no data for the Budget sheet


'Save the workbook as MBxx for new year
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "MB" & Right(Sheets("Master").Range("C1").Value, 2) & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                          CreateBackup:=False
'-------> The new workbook gets created and the all data is gone from the saved workbook 


'-> Clear data in Details sheet
    With Sheets("Details")
        Set tbl = .ListObjects("tbl_Details5")
        ' Clear any filters
        tbl.Range.AutoFilter
        'remove all records leaving one row ready to go
        'Delete all table rows except first row
        With tbl.DataBodyRange
          If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
          End If
        End With
        'Clear out data from first table row
          tbl.DataBodyRange.Rows(1).ClearContents
    End With


' prep Budget sheet
    With Sheets("Budget")
    
    '           -> Clear Gudgeted income data
        .Range("D4:E7").ClearContents
    '           -> Clear Exp & Projedtion data
        .Range("D9:E23").ClearContents
    '           -> Clear Notes
        .Range("F3:F33").ClearContents
    '           ->Clear comments
        .Range("A4:F33").ClearComments
        
        'header for previous    - new year months already filled in
        .Range("A2").Value = "Fiscal  " & Format(DateAdd("yyyy", -1, Sheets("Master").Range("C1")), "m/d/yyyy")
        'header for current
        .Range("D2").Value = "Fiscal  " & Format(Sheets("Master").Range("C1"), "m/d/yyyy")
    
'-> Get totals from prev budget into Budget sheet (How?)
'-------> data in the Master sheet was cleared at the start of this macro so the cell will be filled with zeros
        .Range("P1") = ThisWorkbook.Path
        .Range("Q1") = "MB" & Right(Sheets("Master").Range("C1"), 2) - 1 & ".xlsm"
        .Range("R1") = "Master"
        
        Set rng = .Range("T1", .Range("T" & .Rows.Count).End(xlUp))
        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
        Next cel
    End With


'Save the workbook
    ActiveWorkbook.Save
    
End Sub

I might be able to fix some of the things I found that seem to make this macro fail on this end; like clearing the Master sheet after setting up the Budget sheet, but I don't know how to convert data created by a formula into hard numbers, or how to create an offset to add a space to the SafeAs filename, to archive the current workbook, etc., not yet anyway, that's why I'm studying VBA, so I can do more of that stuff on my own.

 

[NoSparks]

I use that macro exactly as copied and it works for me.

Here's another approach you might want to look at.
Have changed the order of things and added some message boxes and a couple of Stop instructions with comments where you can check to see if what should happen actually does.

Sub Setup_for_NewYear_v3()

    Dim rng As Range, cel As Range, str As String
    Dim yr As String, tbl As ListObject
    Dim OrigName As String, NextName As String
    Dim response As Integer
    Dim Msg As String, Title As String
    
' activate the Master sheet
    Sheets("Master").Select
' name of current workbook
    OrigName = Split(ThisWorkbook.Name, ".")(0)
' save the current workbook
    ActiveWorkbook.Save
    MsgBox "The current  " & OrigName & "  has been saved."

Stop
' manually check the folder to see if the time of the file makes sense and
' that no file for the new year exists yet

' Save the workbook again as MBxx for new year
    yr = Right(OrigName, 2) + 1
    NextName = "MB" & yr
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & NextName & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                          CreateBackup:=False

Stop
' at this point the VBAProject on the left should have changed from MB18 to MB19
' and the new MB19 should exist in the folder

Checkforfile:
    'check that new year file exists
        If Not Dir(ThisWorkbook.Path & "\" & NextName & ".xlsm") = vbNullString Then
            MsgBox "Workbook " & NextName & " has been created."
            'check that newly saved file is active
            If ActiveWorkbook.Name = NextName & ".xlsm" Then
                MsgBox "Will now clear " & NextName & " for use."
            Else
                MsgBox "The active workbook is not  " & NextName & vbLf & _
                       "Will now terminate the macro."
                Exit Sub
            End If
        Else
            'check again?
            Msg = ThisWorkbook.Path & "\" & NextName & ".xlsm" & vbLf & "does not exist" & vbLf & "what now?"
            Title = "Problems"
            response = MsgBox(Msg, 5, Title)
            'Test which button is pressed
                Select Case response
                Case 4  'button VB numeric constant for retry
                    GoTo Checkforfile
                Case 2  'button VB numeric constant for cancel
                    MsgBox "Will now exit this sub"
                    Exit Sub
                End Select
        End If
        
'***********************************
' All clearing is done from here on
'***********************************

'change dates and clear Master sheet
With Sheets("Master")
    'Dates
    For Each cel In .Range("C1:N1")
        cel.Value = DateAdd("yyyy", 1, cel.Value)
    Next cel
    'Clear data
    .Range("D3:N11").ClearContents
    .Range("C13:N14").ClearContents
    .Range("C35:N40").ClearContents
    .Range("C3:N40").ClearComments
    'Change quarters year
    .Range("P1").Value = "1st  quarter " & Year(.Range("C1").Value)
    .Range("Q1").Value = "2nd quarter " & Year(.Range("C1").Value)
    .Range("R1").Value = "3nd quarter " & Year(.Range("N1").Value)
    .Range("S1").Value = "4th   quarter  " & Year(.Range("N1").Value)
End With

'-> Clear data in Details sheet
    With Sheets("Details")
        Set tbl = .ListObjects("tbl_Details5")
        ' Clear any filters
        tbl.Range.AutoFilter
        'remove all records leaving one row ready to go
        'Delete all table rows except first row
        With tbl.DataBodyRange
          If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
          End If
        End With
        'Clear out data from first table row
          tbl.DataBodyRange.Rows(1).ClearContents
    End With

' prep Budget sheet
    With Sheets("Budget")
    '           -> Clear Gudgeted income data
        .Range("D4:E7").ClearContents
    '           -> Clear Exp & Projedtion data
        .Range("D9:E23").ClearContents
    '           -> Clear Notes
        .Range("F3:F33").ClearContents
    '           ->Clear comments
        .Range("A4:F33").ClearComments
        
        'header for previous    - new year months are already filled in
        .Range("A2").Value = "Fiscal  " & Format(DateAdd("yyyy", -1, Sheets("Master").Range("C1")), "m/d/yyyy")
        'header for current
        .Range("D2").Value = "Fiscal  " & Format(Sheets("Master").Range("C1"), "m/d/yyyy")
    
'-> Get totals from prev budget into Budget sheet (How?)
        .Range("P1") = ThisWorkbook.Path
        .Range("Q1") = "MB" & Right(Sheets("Master").Range("C1"), 2) - 1 & ".xlsm"
        .Range("R1") = "Master"
        
        Set rng = .Range("T1", .Range("T" & .Rows.Count).End(xlUp))
        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
        Next cel
    End With

'Save the workbook
    ActiveWorkbook.Save
    
End Sub