Copy Cell Data from Specific Columns w/Conditions to other Tab

[tirk82]

Hi there folks!

Hoping you can assist with some VBA code please; code only no formulas in cells.

Logic should be as follows....

1. In sheet "Rec", for all cells in Column C, under Column Heading "Ticket#" that contain a value, copy those cells and the corresponding cell in Column A. If blank, do not copy just skip.

2. In sheet "Ticket# Index", Paste those values in Column A & B, under the column headings "unique id" & "ticket#". However, paste the new data in the first available blank row, so not to overwrite the existing data.

For example with the below....

In Rec, copy [A2&C2] [A5&C5]
In Ticket# Index, Paste in [A4&B4] & [A5&B5],so not to overwrite the existing data in A2:B3

SHEET NAME = REC

	A	B	C
1	Unique ID	Status	Ticket#
2	72971US1L336000 <tbody> </tbody>	Blah blah	56958435 <tbody> </tbody>
3	72971US1L041618 <tbody> </tbody>	blah blah
4	73137US1L142317 <tbody> </tbody>	blah blah
5	73198US1L041667 <tbody> </tbody>	blah blah	6889573

<tbody>
</tbody>

SHEET NAME = Ticket# Index

	A	B
1	Unique ID	Ticket#
2	1234556789 (existing data)	6047496 (existing data)
3	9087374636 (existing data)	005820104 (existing data)
4	72971US1L336000	56958435
5	73198US1L041667	6889573
6

<tbody>
</tbody>

Thank you so much for you're assistance. It's very much appreciated.

Tom

 

[mumps]

Hi Tom. Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Rec").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("C2:C" & LastRow)
        If rng <> "" Then
            rng.Copy Sheets("Ticket# Index").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            rng.Offset(0, -2).Copy Sheets("Ticket# Index").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[tirk82]

hi tom. Try:

sub copydata()
    application.screenupdating = false
    dim lastrow as long
    lastrow = sheets("rec").cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row
    dim rng as range
    for each rng in range("c2:c" & lastrow)
        if rng <> "" then
            rng.copy sheets("ticket# index").cells(rows.count, "a").end(xlup).offset(1, 0)
            rng.offset(0, -2).copy sheets("ticket# index").cells(rows.count, "b").end(xlup).offset(1, 0)
        end if
    next rng
    application.screenupdating = true
end sub

sweet ! That works! You da man!

 

[mumps]

My pleasure.