Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding and copy info (xp)

    I am having a problem.
    I have attached a file to help me.
    I want to go down column R and every time I find a value above "0" I want to copy certian things off that row onto a different sheet.

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

    Re: finding and copy info (xp)

    Which "certain things" do you want to copy, what sheet do you want to copy them to, and where on that sheet?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding and copy info (xp)

    Rows E, G, and K:O to a second page (which is allready created called collected data) to the following cells in row 5 (after the first row of info is inputed there is a row insereted is that the most current info is on top; that way all the info copied is into the same row) E will go into A G into B K:O into C:G

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

    Re: finding and copy info (xp)

    Try this macro. You can adapt it if necessary.

    Sub CopyInfo()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngMaxRow As Long
    Dim lngTargetRow

    Set wshSource = Worksheets("Sheet1 (2)")
    Set wshTarget = Worksheets.Add

    lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
    For lngSourceRow = 1 To lngMaxRow
    If wshSource.Range("R" & lngSourceRow) > 0 Then
    lngTargetRow = lngTargetRow + 1
    wshSource.Range("E" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("A" & lngTargetRow)
    wshSource.Range("G" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("B" & lngTargetRow)
    wshSource.Range("K" & lngSourceRow & ":O" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("C" & lngTargetRow)
    End If
    Next lngSourceRow

    Set wshTarget = Nothing
    Set wshSource = Nothing
    End Sub

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

    Re: finding and copy info (xp)

    Oops - you changed the requirements at the same moment I posted my macro. Here is a new one.

    Sub CopyInfo()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngMaxRow As Long
    Const lngTargetRow = 5

    Set wshSource = Worksheets("Sheet1 (2)")
    Set wshTarget = Worksheets("Collected Data")

    lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
    For lngSourceRow = 1 To lngMaxRow
    If wshSource.Range("R" & lngSourceRow) > 0 Then
    wshTarget.Rows(lngTargetRow).Insert
    wshSource.Range("E" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("A" & lngTargetRow)
    wshSource.Range("G" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("B" & lngTargetRow)
    wshSource.Range("K" & lngSourceRow & ":O" & lngSourceRow).Copy _
    Destination:=wshTarget.Range("C" & lngTargetRow)
    End If
    Next lngSourceRow

    Set wshTarget = Nothing
    Set wshSource = Nothing
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding and copy info (xp)

    What does this line do:
    lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
    I am assuming that it is assiging 1 up from the last row to IngMaxRow
    Could you change ("R65536") to ("R200") since this is the last possable row for information?
    sorry for being a pain but I learn better if I can rip a piece of code apart and make it do something else, then I inderstand it better.
    thanks

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

    Re: finding and copy info (xp)

    Range("R65536").End(xlUp) is the last filled (non-blank cell) in column R. It is the cell that would be selected if you clicked in R65536 (row 65536 is the bottom row of the worksheet), then pressed End followed by the Up arrow. So Range("R65536").End(xlUp).Row is the row number of the last filled cell in column R. This technique is very useful when you're dealing with a variable amount of data, and you don't know in advance how many rows will be populated. End(xlUp) will always find the last filled cell, whether it's in row 4, row 1000 or row 23000.

    (Similarly, you can use Range("IV4").End(xlToLeft) to find the last filled cell in row 4.)

Posting Permissions

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