Print based on sheet names (dates)

Crs2285

New Member
Joined
Sep 5, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Welcome to the MrExcel board!

You could give this macro a try.

VBA Code:
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
 
Upvote 0
Solution
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
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
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
 
Upvote 0
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 . :eek:
 
Upvote 0
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 . :eek:
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?
 
Upvote 0
is the a easy way to fix that
Yes

New Dim statement at the top
VBA Code:
Dim bStarted As Boolean

Then alter the selection section like this
VBA Code:
      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
 
Upvote 0
Thank you so much

That works perfectly really appreciate your help and time

Chris
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top