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]

Great! Thanks.

I will study the new approach to see if I am able to understand what is going on, then give it a try and see what happens, and let you know.

Again, thanks so much for you continues support on this project, I do so appreciate your patient indulgents!

 

[mikecox39]

the good news is that MB18 got saved; showing current time and MB19 got created and opened. I wonder if a line could be added to close MB18, so that only MB19 remained on screen?

The bad news is that there was a debug error in the If not Dir(thisworkbook...) line and the macro shut down after MsgBox "Workbook " & NextName & " has been create

Here's a screenshot and the saved MB18 file.

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

 

[NoSparks]

In the end I got a messageBox Workbook is not will now terminate

that's a result of If ActiveWorkbook.Name = NextName & ".xlsm" being False, but you can't get there with the Run-time error you show. Did the error go away after waiting a while ?

I wonder if a line could be added to close MB18

At the second Stop, are you able to do that manually and make MB19 the active workbook ?

 

[mikecox39]

that's a result of If ActiveWorkbook.Name = NextName & ".xlsm" being False, but you can't get there with the Run-time error you show. Did the error go away after waiting a while ?

At the second Stop, are you able to do that manually and make MB19 the active workbook ?

Here is a blow by blow account of what happens when I run the macro

F5
Currenrt MB18 saved with correct time
Stop
F5
Stop
MB19 in folder and open on DT
Stop
F5
Run-time error 52
Debug
If Not Dir highlighted
F5
Run-time error 52
End
Cursor at If Not Dir
F5
Current MB19 has been saved
OK
Cursor to Stop above 'manually check the folder fo see if the time of the files makes sense; it does
F5
Cursor stops above 'check that new year file exists; it does
F5
Run-time error 52
End
F5
MB20 created

 

[mikecox39]

Something happened to my edited post, after saving it I checked and discovered the good news got lopped off!

After I posted that scenario I got to thinking about the line debug highlighted. It was related to the OneDrive Dir and I began to wonder what would happen if I took the file out of the cloud, off OneDrive, and put in on my HD. When I did that the macro ran perfectly!

Mission Accomplished!

I am in your debt. Big Time!

 

[NoSparks]

That's great, and just before I rue the day....

I'm sure somebody on the forum would know how to adjust things to operate with the files 'in the cloud' but it's not me.
If you happen to post that as a question I'll be watching with great interest.

Oh, and to make those numbers on the result sheet static instead of formulas, adding an instruction to the loop works for me.

        'putting formulas into the cells
        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
            'overwrite formula with displayed value
            .Range(cel).Value = .Range(cel).Value
        Next cel

Good luck with the project.

 

[mikecox39]

That's great, and just before I rue the day....

I'm sure somebody on the forum would know how to adjust things to operate with the files 'in the cloud' but it's not me.
If you happen to post that as a question I'll be watching with great interest.

Ok, I'll post a question about that. I'm thinking it may be a timing issue. We'll see.

Oh, and to make those numbers on the result sheet static instead of formulas, adding an instruction to the loop works for me.

        'putting formulas into the cells
        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
            'overwrite formula with displayed value
            .Range(cel).Value = .Range(cel).Value
        Next cel

Good luck with the project.

Thanks. I could probably figure it out but, just in case, exactly where should I insert it?.

 

[NoSparks]

it's one line, exactly where shown in the loop that puts in the formulas

        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
            'overwrite formula with displayed value
            .Range(cel).Value = .Range(cel).Value
        Next cel

 

[mikecox39]

it's one line, exactly where shown in the loop that puts in the formulas

        For Each cel In rng
            str = "='" & .Range("P1") & "\[" & .Range("Q1") & "]" & .Range("R1") & "'!" & cel.Offset(, 1)
            .Range(cel).Formula = str
            str = ""
            'overwrite formula with displayed value
            .Range(cel).Value = .Range(cel).Value
        Next cel

Got it, thanks.

I just posted the OneDrive question

 

[NoSparks]

Not much resulting from the OneDrive question...

Perhaps a delay between SaveAs and checking if the file is there will help, try inserting a 5 second delay (or more) and see if it makes any difference.

' 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

    Application.Wait(Now + TimeValue("00:00:05"))

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

PS: when replying, just click Reply not Reply With Quote.
Thanks