Print based on sheet names (dates)

[Crs2285]

Hello

looking for some support and not even sure if it is possible

Am looking for a way to use macro to ask the user to enter 2 dates and it will print the sheets between these dates sounds simple but am guessing not as easy to do


background
Am using A single workbook with each sheet named as a date one for each week of the year lalways stars on a Sunday and in format of dd-mmm-yy (5-Sep-21)
User must be able to continue to add sheets as weeks move though the year

ideally want to print 2 sheets to one page however I think I could work this out with some research

Any help or ideas how how to achieve the above would me amazing spend most of the day looking for help

thanks
Chris

 

[Peter_SSs]

Welcome to the MrExcel board!

You could give this macro a try.

Sub PrintBetweenDates()
  Dim Resp As String
  Dim aDates As Variant
  Dim ws As Worksheet
  Dim StartDate As Date, EndDate As Date, SheetDate As Date
  
  Resp = Application.InputBox("Enter 2 dates separated by a space", "Date Selection")
  aDates = Split(Resp)
  If UBound(aDates) = 1 Then
    If Not IsDate(aDates(0)) Or Not IsDate(aDates(1)) Then
      MsgBox "Cannot recognise """ & Resp & """ as two dates"
    Else
      StartDate = Application.Min(CDate(aDates(0)), CDate(aDates(1)))
      EndDate = Application.Max(CDate(aDates(0)), CDate(aDates(1)))
      For Each ws In Worksheets
        If IsDate(ws.Name) Then
          SheetDate = CDate(ws.Name)
          If SheetDate >= StartDate And SheetDate <= EndDate Then ws.PrintOut
        End If
      Next ws
    End If
  Else
    MsgBox "Two dates not entered"
  End If
End Sub

 

[Crs2285]

Amazing Thank you so much you made that look easy and it works perfectly

will do some research on the best way to get this to print 2 sheets to 1 page and am sorted

thanks again for your time and help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Crs2285]

You're welcome. Thanks for the follow-up.

Could I ask a question
If my understanding is correct your macro finds the sheets between the selected dates and then prints and moves to the next sheet and prints. Making it difficult to print more then one sheet to a piece of paper.

would I been moving in the right direction if I changed the ws.PrintOut to select each sheet then added a print command at the end of the macro or wouldn’t this work?

apologies am still learning how things work
thanks again and sorry to take any more of your time

chris

 

[Peter_SSs]

would I been moving in the right direction if I changed the ws.PrintOut to select each sheet then added a print command at the end of the macro

That should be fine.
I would suggest also to deselect any grouped sheets after you have done the print. In my experience, leaving sheets grouped is a recipe for problems later .

 

[Crs2285]

That should be fine.
I would suggest also to deselect any grouped sheets after you have done the print. In my experience, leaving sheets grouped is a recipe for problems later .

perfect thank you again the deselect is a good idea

one last question i have used ws.Select Replace:=False to select each worksheet however only issue is the worksheet the marco was started on remain selected even if its not in the date range is the a easy way to fix that or just make sure the marco is started on a worksheet that needs to be printed?

 

[Peter_SSs]

is the a easy way to fix that

Yes

New Dim statement at the top

Dim bStarted As Boolean

Then alter the selection section like this

      For Each ws In Worksheets
        If IsDate(ws.Name) Then
          SheetDate = CDate(ws.Name)
          If SheetDate >= StartDate And SheetDate <= EndDate Then
            ws.Select Replace:=bStarted = False
            bStarted = True
          End If
        End If
      Next ws

 

[Crs2285]

Thank you so much

That works perfectly really appreciate your help and time

Chris

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.