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


    Code
    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


    Hi,


    I'm not 100% sure what you want to do. But to print a sheet based on a cell-value:


    Code
    Sheets(range("I1").value).PrintOut


    If you write a loop you can make it flexibel, loop through the column and print every name.

  • 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.


    Code
    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


    Code
    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 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.



    Code
    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


    I suspect it may not be a coincidence that the 3 sheets that won't print all have numeric values in column H whereas the ones that do print are text.


    Try converting the contents of the cell to text to represent the sheet name, i.e.

    Code
    Sheets(CStr(Range("H" & intRow).Value)).PrintOut


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!