PRINT OUT USED SHEETS ONLY IN EXCEL 1

[DIVINEDAR0956]

I have the following code in excel:

Sub Print_Year1_Sheets()
Sheets("PROJECT_INFORMATION&quot.Activate
Do While cursheet < last_sheet
Go_NextSheet
If Range(&quot;T44&quot > 0 Then
Set_PrintSettings
Range(&quot;A1:R46&quot.Select
Selection.PrintOut Copies:=1
End If
Loop
End Sub

My problem is that it will only print year1 and I have 5 years to print. The range is different for all 5 years and each sheet has a 5 year span. For some reason This will print year 1 only and not the other years.

Please help!!!

 

[DaleWatson123321]

What I'd recommend is that you have a &quot;main&quot; routine, such as the following, from which you'll call the subroutines for each of the 5 years.

Sub Print_Active_Sheets()
cursheet = 0
Application.ScreenUpdating = False
last_sheet = Sheets(&quot;LastSheetName&quot.Index - 1
Print_Year1_Sheets
Print_Year2_Sheets
Print_Year3_Sheets
Print_Year4_Sheets
Print_Year5_Sheets
Application.ScreenUpdating = True
End Sub

For &quot;LastSheetName&quot; above, replace this with the name of the sheet that immediately follows the last sheet from which you want to print.

Sub Print_Year1_Sheets()
Sheets(&quot;PROJECT_INFORMATION&quot.Activate
Do While cursheet < last_sheet
Go_NextSheet
If Range(&quot;T44&quot > 0 Then
Set_PrintSettings
Range(&quot;A1:R46&quot.Select
Selection.PrintOut Copies:=1
End If
Loop
End Sub

For each subroutine, like above, you'll need to have the process return to the starting point (&quot;PROJECT_INFORMATION&quot; sheet).

You'll need to modify the Ranges for each of the Years, and also ensure that the individual sheets have the necessary formulas (&quot;T44&quot; for example) - that will cause the condition to be &quot;TRUE&quot; (a value >0) for when you want a sheet printed.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DIVINEDAR0956]

Thank you Dale for your help. I'm just about finish. One more question for you.

How can I fill in a blank cell with zero. I want to fill in all my unused cells in my HRS_ENTER worksheet with zero so when I extract the information it also extracts the zero for the other months.

Again Thank you?

 

[DaleWatson123321]

I believe the extraction routine ALREADY extracts the zeros for the other months.

I expect the problem could exist simply because the sheet might be configured to NOT show &quot;Zero values&quot;.

If this is the case, you just need to use: Tools - Options. Then under the &quot;View&quot; tab, click on &quot;Zero values&quot; which will place a check-mark in the box to its left.

I hope this resolves the problem.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca