Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MacroTroubles (2000/2003)

    I have a checklist with a macro that when -on the audit sheet the "NO" box is checked- the macro copies the row to the punchlist sheet. This works fine except when I have multiple "NO's" checked in consecutive rows. I think it is because I use "End Down", -this occurs in the "Create Punchlist Macro"- to find the next row with an "X" in it, I just don't no what command to use in place of this to make it work. As long as the x is not in consecutive rows all works ok. Hope this explains it. I tried attaching even a cut down version but the file is still too big. Here's the code.
    Can anyone please help???

    Selection.End(xlDown).Select
    ActiveCell.EntireRow.Select
    Selection.Copy
    Sheets("PUNCHLIST").Select
    Range("C3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.EntireRow.Select
    ActiveSheet.Paste

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MacroTroubles (2000/2003)

    Can you explain what the check boxes have to do with this code?

    (Did you try zipping the workbook?)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    I may have misled you, I am not using a check box. I am placing an X in a cell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MacroTroubles (2000/2003)

    Can you explain how the "X"s in cells are related to the code you posted?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    I zipped it as you said.
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MacroTroubles (2000/2003)

    This piece of code should work assuming the X is in column D. It selects Punchlist and adds it to the last row in the sheet


    Sub CheckStuff()

    Dim lngMaxRow As Long

    lngMaxRow = Range("D65536").End(xlUp).Row


    Cells(lngMaxRow, 4).Select
    Rows(lngMaxRow).Select

    Selection.Copy
    Sheets("PUNCHLIST").Select

    lngMaxRow = Range("A65536").End(xlUp).Row + 1

    Rows(lngMaxRow).Select


    ActiveCell.EntireRow.Select
    ActiveSheet.Paste

    End Sub
    Jerry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MacroTroubles (2000/2003)

    I hope Jerry's suggestion helps. If not, please explain what exactly you want to do. I don't understand your code, I'm afraid.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    On the audit sheet, if I put an "X" in cell E13,E15,E17 and E19. Click on erase punchlist, then click on create punchlist. I will have four items on the punchlist. This part works fine.

    However, If I put an "X" in E13,E14,E15 and E16 (Consecutive cells)I only get the E13 item and the E16 item. I need all items with an x in column "E" to transfer to the punchlist.
    Thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MacroTroubles (2000/2003)

    Instead of selecting cells, use a loop like this:
    <code>
    Dim r As Long
    For r = 13 To Sheets("Audit").Range("E65536").End(xlUp).Row
    If Not Sheets("Audit").Range("E" & r) = "" Then
    ' Code to transfer data from row r to the Punchlist sheet goes here
    End If
    Next r</code>

  10. #10
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    Thanks, I'll try to implement these suggestions.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    I tried to get the code you supplied integrated into what I have, but I'm not knowledable enough to make it work.
    Putting aside the code I have allow me to try explain what I am trying to do.

    On the Audit sheet in columns D,E, & F (Yes,No,NA) we input an X to indicate the result of the question in column C.
    When an X is placed in the NO column, I need to copy the contents of the C and I cells of the same row to the punchlist sheet. To the appropriate columns.
    Then return to the audit sheet and check for the next instance that an X occurs in the E column and repeat the process.
    This will continue until all the rows that have an X in column E on the audit sheet is on the punchlist sheet.
    This will result in a checklist items (column C) and Remarks (Column I) in the punchlist that had an X in the NO column.

    Our checklist we use has over 400 items and so I need to do this to save the time of copying and pasting all the items individually.

    Thankyou for your time and patience. I really appreciate it.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MacroTroubles (2000/2003)

    Try this:

    Sub CheckStuff()

    Dim lngMaxRow As Long

    Dim r As Long


    For r = 15 To Sheets("Audit").Range("E65536").End(xlUp).Row
    If Not Sheets("Audit").Range("E" & r) = "" Then

    Range(Cells(r, 3), Cells(r, 9)).Select

    Selection.Copy
    Sheets("PUNCHLIST").Select

    lngMaxRow = Range("C65536").End(xlUp).Row + 1

    Rows(lngMaxRow).Select


    ActiveCell.EntireRow.Select
    ActiveSheet.Paste
    Sheets("Audit").Select
    End If
    Next r

    lngMaxRow = Range("D65536").End(xlUp).Row



    End Sub
    Jerry

  13. #13
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MacroTroubles (2000/2003)

    I ran the code and received an error 1004.

    Thanks for the help.
    Attached Images Attached Images

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MacroTroubles (2000/2003)

    Try this version. It doesn't select any cells.
    <code>
    Sub CreatePunchlist()
    Dim lngMaxRow As Long
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Application.ScreenUpdating = False
    lngTargetRow = Worksheets("Punchlist").Range("C65536").End(xlUp). Row
    lngMaxRow = Worksheets("Audit").Range("E65536").End(xlUp).Row
    For lngSourceRow = 13 To lngMaxRow
    If Not Worksheets("Audit").Range("E" & lngSourceRow) = "" Then
    lngTargetRow = lngTargetRow + 1
    Worksheets("Punchlist").Range("C" & lngTargetRow) = _
    Worksheets("Audit").Range("C" & lngSourceRow)
    Worksheets("Punchlist").Range("D" & lngTargetRow) = _
    Worksheets("Audit").Range("I" & lngSourceRow)
    End If
    Next lngSourceRow
    Application.ScreenUpdating = True
    End Sub</code>

  15. #15
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MacroTroubles (2000/2003)

    I am using the original spreadsheet that you posted in this thread so I cannot see why you are getting the error unless you have added stuff in the interim period. There is a lot of data in the punchlist that is superfluous lower down in the sheet ...so I don't know why you are getting, just that I have it working here <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Try the one I have attached
    Jerry

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •