Posts by jandrew

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

Sorry for the dely in response. The code works! Thank-you for taking a look
Jeff

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.

Sub CopyRows()
    Worksheets("sheet1").Select
    FinalRow = Range("M65536").End(xlUp).Row
    ' Find the last row of data
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column M
        ThisValue = Range("M" & x).Value
        ' could be any value
        If ThisValue = "yes" Then
            Worksheets("sheet2").Range("A" & x & ":L" & x).Copy
            Worksheets("sheet1").Select
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            ActiveSheet.PasteSpecial = xlValues
            Worksheets("sheet1").Select
        End If
    Next x
End Sub

Re: Chang Print Area Based On Cell Change

Thanks Kenneth that was the problem, I've always had trouble seeing the forest for the trees.
Cheers
Jeff

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.

Private Sub Worksheet_Change(ByVal Target As Range)
   If IsEmpty(Target) Then Exit Sub
      If Not Intersect(Target, Range("C12,C74,C136,C198,C260")) Is Nothing Then
       Run "ChangePrintArea"
    End If
    
      
End Sub
Sub ChangePrintArea()
 ' if cell C260 on TENTRY sheet has a value in it
    If Worksheets("TENTRY").Range("C260").Value > 0 Then
         ' make the print area from cell A18 to cell E288  (5 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$288"
         ' if cell C195 has a value in it then
    ElseIf Worksheets("TENTRY").Range("C198").Value > 0 Then
         ' make the print area from cell A18 to E229 ( 4 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$229"
    ElseIf Worksheets("TENTRY").Range("C136").Value > 0 Then
         ' make the print area from cell A18 to E170 (3 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$170"
    ElseIf Worksheets("TENTRY").Range("C74").Value > 0 Then
         ' make the print area from cell A18 to E111 ( 2 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$111"
    ElseIf Worksheets("TENTRY").Range("C12").Value > 0 Then
         ' make the print area from cell A18 to E67
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$67"
    End If
     
End Sub

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.

Sub ChangePrintArea()
     ' if cell C254 on TENTRY sheet has a value in it
    If Worksheets("TENTRY").Range("C256").Value <> "" Then
         ' make the print area from cell A18 to cell E286  (5 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$286"
         ' if cell C195 has no value in it then
    ElseIf Worksheets("TENTRY").Range("C195").Value <> "" Then
         ' make the print area from cell A18 to E227 ( 4 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$227"
    ElseIf Worksheets("TENTRY").Range("C134").Value <> "" Then
         ' make the print area from cell A18 to E168 (3 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$168"
    ElseIf Worksheets("TENTRY").Range("C73").Value <> "" Then
         ' make the print area from cell A18 to E109 ( 2 PAGES)
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$109"
    ElseIf Worksheets("TENTRY").Range("C12").Value <> "" Then
         ' make the print area from cell A18 to E70
        Worksheets("TQUOTE").PageSetup.PrintArea = "$A$18:$E$70"
    End If
     
End Sub

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?

Sub ChangePrintArea()
     ' if cell A254 has a value in it then
    If Worksheets("TQUOTE").Range("A254").Value <> "" Then
         ' make the print area from cell A18 to cell E286 (5 PAGES)
        ActiveSheet.PageSetup.PrintArea = "$A$18:$E$286"
         ' but if cell A195 has no value in it then
    ElseIf Worksheets("TQUOTE").Range("A195").Value <> "" Then
         ' make the print area from cell A18 to E227 ( 4 PAGES)
        ActiveSheet.PageSetup.PrintArea = "$A$18:$E$227"
        ElseIf Worksheets("TQUOTE").Range("A136").Value <> "" Then
         ' make the print area from cell A18 to E168 (3 PAGES)
        ActiveSheet.PageSetup.PrintArea = "$A$18:$E$168"
        ElseIf Worksheets("TQUOTE").Range("A77").Value <> "" Then
         ' make the print area from cell A18 to E109 ( 2 PAGES)
        ActiveSheet.PageSetup.PrintArea = "$A$18:$E$109"
        ElseIf Worksheets("TQUOTE").Range("A18").Value <> "" Then
         ' make the print area from cell A18 to E70
        ActiveSheet.PageSetup.PrintArea = "$A$18:$E$70"
        ElseIf Worksheets("TQUOTE").Range("A18").Value = "" Then
         ' make the print area EMPTY
        ActiveSheet.PageSetup.PrintArea = ""
    End If
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

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.