Automate date range entry

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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!

 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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
 
Last edited:

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
879
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Mar 5, 2014
Messages
237
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

Board Regular
Joined
Mar 5, 2014
Messages
237
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!

(y)
 
Last edited:

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
879
Office Version
2010
Platform
Windows
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.
Code:
        '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

Board Regular
Joined
Mar 5, 2014
Messages
237
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.
Code:
        '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

Well-known Member
Joined
Mar 15, 2013
Messages
879
Office Version
2010
Platform
Windows
it's one line, exactly where shown in the loop that puts in the formulas
Rich (BB code):
        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

Board Regular
Joined
Mar 5, 2014
Messages
237
it's one line, exactly where shown in the loop that puts in the formulas
Rich (BB code):
        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

Well-known Member
Joined
Mar 15, 2013
Messages
879
Office Version
2010
Platform
Windows
Not much resulting from the OneDrive question... :banghead:

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.
Rich (BB code):
' 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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,404
Messages
5,414,218
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top