Is there a better way to write this code...

[bg18461]

Is there a better way to write this code to print certain worksheet
that contain data in some sort of for loop. Any insight would b
appreciated, thanks.

Private Sub CommandButton8_Click()

If Worksheets("Sheet1").Range("Y3").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X3").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y4").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X4").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y5").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X5").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y6").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X6").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y7").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X7").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y8").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X8").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y9").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X9").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y10").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X10").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y11").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X11").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y12").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X12").Value).PrintOut
End If

If Worksheets("Sheet1").Range("Y13").Value = True Then
Worksheets(Worksheets("Sheet1").Range("X13").Value).PrintOut
End If

End Su

 

[Leo Heuser]

One way (untested)

Private Sub CommandButton8_Click()
Dim Cell As Range
Dim DummyRange As Range

Set DummyRange = Worksheets("Sheet1").Range("Y3:Y13")

For Each Cell in DummyRange.Cells
If Cell.Value = True Then
Worksheets(Cell.Offset(0,-1).Value).PrintOut
End If
Next Cell
End Sub

 

[AlfD]

Hi!

Yes: a loop seems a good idea. How do you want the output to look? Al
on one page or 7? How much data is there in the cells you are printing
(1 word will be very different from 500...)

Al

 

[Don Guillett]

maybe this. Change printpreview to printOUT after testing

Sub fori()
With Worksheets("sheet1")
For i = 4 To 13 Step 1
If .Cells(i, "y") <> "" Then .Cells(i, "x").PrintPreview
Next i
End With
End Sub

 

[Don Guillett]

After looking at this again, I think you have a sheet name in col Y and an X
or something in col X to print the worksheet desired. If so, then this
should work
sheet22 x
jobs x
aaabfd
bbbbdd x

Sub foriA()
With Worksheets("sheet1")
On Error Resume Next
For i = 4 To 13 Step 1
x = Cells(i, "x")
If .Cells(i, "y") <> "" Then Sheets(x).PrintOut
Next i
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

maybe this. Change printpreview to printOUT after testing

Sub fori()
With Worksheets("sheet1")
For i = 4 To 13 Step 1
If .Cells(i, "y") <> "" Then .Cells(i, "x").PrintPreview
Next i
End With
End Sub

 

[bg18461]

Every sheet will print on a different sheet of paper, if the value i
true I want to print that particular sheet. All the sheets are alread
formatted, so I just want it to print of said cell value is true. Sa
if I had a page counter in the footer, and I printed 4 Sheets.

Sheet1 - 2 pages to print
Sheet2 - 1 page to print
Sheet3 - 3 pages to print
Sheet4 - 1 page to print

So my footer should read Page 1 of 7 if I can contain this in a loop.
As it stand right now, for every sheet that the value is true i
initiates the print command. So the counter would on show the numbe
of pages per sheet, not per selection.

The the Values in cells x3:x13 refer to sheet names, and y3:y13 is jus
cells that contain true or false based up conditions I already set.

So if the the value for the sheet is true, I want it to add to th
selection of sheets then initiate the print command once. Thanks fo
your ear

 

[Don Guillett]

Since you are using the word TRUE then change my previous to
If .Cells(i, "y") = True Then Sheets(x).Printout

 

[bg18461]

bump..

 

[Soo Cheon Jheong]

Hi,

Option Explicit
Private Sub CommandButton8_Click()

Dim i As Integer
For i = 3 To 13
With Worksheets("Sheet1").Cells(i, "Y")
If .Value Then Worksheets(.Offset(0, -1).Value).PrintOut
End With
Next

End Sub


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Don Guillett]

Perhaps if you told us EXACTLY what you want to do.

this might be what you want
with Worksheets("Sheet1")
if .cells(i,"Y")<>"" then
or
if .cells(i,"Y")="X" then