Posts by jandrew

    Re: Paste Data To Next Empty Row Based On Cell Contents


    Hi Stephen and Roy
    Thank you for your quick response. I have to admit that I am struggling to teach myself vba and as you can see having difficulty with even the simplest code. Stephen i tired running the macro with your adjustments and it did not work and I can't understand why. I have enclosed a small workbook ( 2 sample sheets with a commnet on sheet 2 if you have a minute take a look and see where I have gone wrong.
    Again any help is greatly appreciated.
    cheers Jeff

    I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc.
    I'm not sure if this is the way to go , any and all help would be greatly appreciated.

    My worksheet change macro fires only when data is entered into one of the target cells and NOT when I remove the data ( using either clear contents or backspace). I am including my event code and the sub routine associated with it. I am quite new to programming and vba , and struggling. Any help would be greatly appreciated.

    Re: Set Print Area Based On A Condition


    Hi Mark,
    After more testing I realize that if I step through the macro it it will stop at the line of code which is true, and then and only then will the macro update the print area. I don't understand why it does not update whem there is a change in one of the cells on the "TENTRY" worksheet.
    I guess I jumped the gun saying it was working well. My apologies.
    Jeff

    Re: Set Print Area Based On A Condition


    Hi Mark,
    Turns out the cells on "TQUOTE" sheet did have hidden formulas. I edited the code to look at the cells on another sheet "TENTRY" which are linked to the cells on "TQUOTE ". I also removed the lines for print out etc. as I want to bypass the print dialogue . The macro works well .
    You may not realize how difficult all of this can be for a beginner like me, which makes your help invaluable and greatly appreciated.
    Thank-you very much for the assistance.
    Here is the adjusted code.

    Re: Set Print Area Based On A Condition


    Hi Mrk and thank you for your response. I tried editing your code to adjust my print area based on specified cells being empty. For some reason the print area remains at A18 : E286 even when all specified cells are empty. This is how i modified your code. Also does it matter that I have the page set up to have several rows repeat at the top of each page?


    Your help is greatly appreciated
    Jeff

    I have been searching without real success for a soloution to a print area problem. Hopefully this one us easy for those blessed with the more ability than me.
    I have a worksheet named "TQUOTE" with the print area defined as A1:E:286 resulting in 5 pages printing ,however if there are only 3 pages of data I will be left with 2 blank pages being printed. ( along with the header rows which are set to repeat for each page ). I should mention this worksheet is included in a list of sheets to print based on the response to a user form.
    Would it be possible to have the print area change based on the an empty cell that related to the next page break? eg. If the page break for page 1 ends at row 55, if a cell in row 56 were empty then set the print area to be A1:E55
    If the page break for page 2 ends at row 85, if a cell in row 86 were empty then set the print area to be A1:E85 and so on ??
    I do not have the skills yet to write this macro , and i bow to thiose that do !<smirk>
    any and all help would be greatly appreciated.
    Jeff

    Re: Print Chosen Sheets


    Wow! what an overwhelming response! Thanks to Dave Hawley and Windy 58 , both suggestions worked quite well. Thanks also to norie for your patience and to everyone else that replied. You're the best!

    Re: Print Selected Sheets


    I tried naming the sheets that I wanted to choose from ,but I don't know what i'm doing in VBA quite yet. Here is a copy of Dave's code. Thank
    you.
    [vba]
    Private Sub CheckBox1_Click()
    Dim iloop As Integer


    For iloop = 1 To ListBox1.ListCount
    ListBox1.Selected(iloop - 1) = CheckBox1.Value
    Next
    End Sub


    Private Sub CommandButton1_Click()
    Unload Me
    End Sub


    Private Sub CommandButton2_Click()
    Dim iloop As Integer
    For iloop = 1 To ListBox1.ListCount
    If ListBox1.Selected(iloop - 1) = True Then
    Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
    ListBox1.Selected(iloop - 1) = False
    End If
    Next
    End Sub


    Private Sub UserForm_Initialize()
    Dim sSheet


    For Each sSheet In Sheets
    If sSheet.Type = 3 Then 'Chart sheet
    ListBox1.AddItem sSheet.Name
    ElseIf WorksheetFunction.CountA(sSheet.Cells) > 0 Then
    ListBox1.AddItem sSheet.Name
    End If
    Next sSheet
    End Sub[/vba]

    I have a workbook containing over 75 sheets. I would like to print only a select few of those sheets using a macro. I did download the "sheets to print" workbook submitted by Dave Hawley ,and have tried (in vain) to adapt the code so that it does not display ALL of the workhseets in the userform box. I want to limit the sheets displayed in this box to onnly those that I name. Any and all help would be greatly appreciated.