Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Houston, TX, USA
    Posts
    36
    Thanks
    2
    Thanked 0 Times in 0 Posts

    WorkSheet macro problem (Excel 2000)

    I am having difficulty with an Excel macro. I have an XLS with about 500 rows. (Col A is a unique ID). I have to from time to time extract various rows (yes, based on unique ID) and copy them to another sheet. I may do this three or 10 times/day. The content is never the same. I am trying to write a macro to let me key in each ID (press enter) and then when done, press a button, close. Each ID's row would be copied to sheet 2 and can be handled accordingly. This doesn't seem like a complicated macro, but being new to VBA, I am having a devil of a time. I've gotten it to clear sheet 2 before I start, but am having trouble getting it to keep asking for IDs until I stop.

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkSheet macro problem (Excel 2000)

    The code below assumes that the IDs are entered as strings in Column A on Sheet1. It also assumes that you want to prompt the user to enter each ID that is to be copied. It should give you a start.

    <pre>Public Sub CopyRows()
    Dim lLastRow As Long, I As Long, J As Long
    Dim strID As String
    Worksheets("Sheet2").Cells.ClearContents
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    J = 0
    Do While True
    strID = InputBox("Enter next ID")
    If strID = "" Then Exit Do
    For I = 0 To lLastRow
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strID Then Exit For
    Next I
    If I > lLastRow Then
    MsgBox "ID not found."
    Else
    Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Copy _
    Destination:=Worksheets("Sheet2").Range("A1").Offs et(J, 0)
    J = J + 1
    End If
    Loop
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Houston, TX, USA
    Posts
    36
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: WorkSheet macro problem (Excel 2000)

    Thank you Legare. Yes, I should have indicated they were strings. I'll change it up and let you know tomorrow. Thanks again
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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