Automate date range entry
Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 50

Thread: Automate date range entry

  1. #31
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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 by mikecox39; Jul 4th, 2019 at 04:47 PM.

  2. #32
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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 by mikecox39; Jul 4th, 2019 at 11:15 PM.

  3. #33
    Board Regular
    Join Date
    Mar 2013
    Posts
    763
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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 ?

  4. #34
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    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

  5. #35
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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!

    Last edited by mikecox39; Jul 5th, 2019 at 04:51 AM.

  6. #36
    Board Regular
    Join Date
    Mar 2013
    Posts
    763
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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.

  7. #37
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    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?.

  8. #38
    Board Regular
    Join Date
    Mar 2013
    Posts
    763
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    it's one line, exactly where shown in the loop that puts in the formulas
    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

  9. #39
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    it's one line, exactly where shown in the loop that puts in the formulas
    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

  10. #40
    Board Regular
    Join Date
    Mar 2013
    Posts
    763
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    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.
    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 by NoSparks; Jul 10th, 2019 at 11:50 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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