Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 50

Thread: Automate date range entry

  1. #21
    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
    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.
    Last edited by mikecox39; Jul 2nd, 2019 at 04:18 AM.

  2. #22
    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

    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

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

    Default Re: Automate date range entry

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


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

  4. #24
    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
    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.
    Last edited by mikecox39; Jul 2nd, 2019 at 03:52 PM.

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

    Default Re: Automate date range entry

    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.
    Last edited by NoSparks; Jul 2nd, 2019 at 05:17 PM.

  6. #26
    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
    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/585de67c7893...cuments/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.
    Last edited by mikecox39; Jul 3rd, 2019 at 02:55 AM.

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

    Default Re: Automate date range entry

    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.

  8. #28
    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 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?

  9. #29
    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 mikecox39 View Post
    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.

    Code:
    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.

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

    Default Re: Automate date range entry

    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.
    Code:
    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

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
  •