Print worksheets based on information in column

I have a workbook with many sheets, all of which derive information from the first sheet. On the first sheet, I enter values from say J1:J29. Each of these values is fed into one of the subsequent worksheets. I would like to have some code that would look at my range, determine which cells are used and print the corresponding worksheets. Write now I have a bunch of If statements that say

If Sheets("Worksheet").Range("j8") > 0 Then
        Sheets("698-ALY W1222").PrintOut
    End If

the problem is that it takes to long as each statement is evaluated and the print command sent.

The values in column J have names in column I and while the names do not exactly match the worksheets, a person could look at the name and match the correct sheet. For instance the name in column I that corresponds to the worksheet name 698-ALY W1222 is 698/ALY.

Thanks
Bryce

Re: Print worksheets based on information in column

I have included the first two sheets of my workbook. On the first sheet I will enter dollar values into column F that will flow through to the other sheets. But not every cell in column F will have a value. I only want to print the worksheets that have values. Also note that on the first worksheet, each cell in the range F8:f25 will have a corresponding worksheet.

And as far as the loop is concerned, I am just learning VBA and would appreciate any help you or anyone else could give.

Thanks
Bryce

Re: Print worksheets based on information in column

Gollem thanks for a qiuck response. I tried the code and it mostly works, but it is encountering an error in the argument following the then statement. I changed the collumns to there actuall location in the real workbook, but I wasnt certain about the W, unless this is short for join the info in column H with I to create the worksheet name, which by the way is great and thank you very much for your effort.

Private Sub cmdPrint_Click()
    Dim intRow          As Integer
    
    For intRow = 8 To 100 'Start from row 8
        If Range("J" & intRow).Value > 0 Then
            Sheets(Replace(Range("H" & intRow).Value, "/", "-") & " W" & Range("I" & intRow).Value).PrintOut
        End If
    Next intRow
End Sub

Re: Print worksheets based on information in column

I figured out what the W was, I guess I never noticed it. Anyway, I made it easier, I just created the sheet names in coulmn H.

I thought I could modify the code to this

Private Sub cmdPrint_Click()
    Dim intRow          As Integer
    
    For intRow = 8 To 100 'Start from row 8
        If Range("J" & intRow).Value > 0 Then
            'Sheets(Range("H" & intRow).Value).PrintOut  'vba doesnt like this
        End If
    Next intRow
End Sub

but vba still does not like this any sugestions gollem?

Re: Print worksheets based on information in column

I figured it out. Thanks Gollem for showing me the loop though, I'll have to try and use something like it else where to get the hang of it.

Re: Print worksheets based on information in column

I am back, it seems I have not figured it out. The problem is that while the code sees the last 3 worksheet names in column H, it won’t print them. I keep getting a run time error. I tried renaming the sheets just to see if there was a difference in the names between what is in column H and what the sheets name is but that didn’t fix it.

I have the code running from 8 to 29 because I don’t want it to loop farther than the last account.

Anyone have any idea, I am stumped. Here is the code and I am attaching a sample worksheet with the three worksheets that wont print and the macro I am using.

For intRow = 8 To 29 'Start from row 8
        If Range("J" & intRow).Value > 0 Then
            Sheets(Range("H" & intRow).Value).PrintOut
        End If
    Next intRow

thanks guys/gals

Re: Print worksheets based on information in column

Batman I bow to your brilliance!!! Thanks man, that worked. I love this forum.