Copying data based on certain criteria

[haas786]

Hi all!

I have a question that I believe you guys can answer. Here it is:

I have 2 tabs in an Excel workbook - one tab ('All Transactions')
contains info for every type of transaction I've done. This gets
continuously updated from other spreadsheets via a macro. There is a
column (Column B) within this tab which describes what type of
transaction it is ("Bound", "Declined", "ANB"). What I want done is to
run a macro which would copy the first 15 columns from this tab with
the any transaction which is labeled as "Bound" in column B and paste
it to the 2nd tab in the workbook ('Bound Transactions'). In essence,
I don't want to copy any deals labeled as "Declined" or "ANB" from
column B. I hope I wasn't too confusing - can someone please help?

Thanks!

 

[Guest]

Try this procedure.

Sub CopyData()
Dim C As Long, ATS As Worksheet, BTS As Worksheet
Set ATS = ThisWorkbook.Worksheets("All Transactions")
Set BTS = ThisWorkbook.Worksheets("Bound Transactions")
For C = 1 To ATS.Cells(ATS.Rows.Count, 2).End(xlUp).Row
If ATS.Cells(C, 2).Value = "Bound" Then
ATS.Range(ATS.Cells(C, 1), ATS.Cells(C, 15)).Copy
Destination:=BTS.Cells((Application.WorksheetFunction.CountA(BTS.Columns(2)))
+ 1, 1)
End If
Next C
End Sub

Hope this helps.

If you don't want a macro or want to adjust the macro a little try playing
around with the advanced filter functionality.

Bill Horton

 

[merjet]

Sub CopyBound()
Dim iR1 As Integer
Dim iR2 As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("All Transactions")
Set ws2 = Sheets("Bound Transactions")
ws2.Cells.Clear
Do
iR1 = iR1 + 1
If ws1.Range("B" & iR1) = "Bound" Then
iR2 = iR2 + 1
ws1.Range("A" & iR1 & ":O" & iR1).Copy _
ws2.Range("A" & iR2)
End If
Loop Until ws1.Range("A" & iR1) = ""
End Sub

Hth,
Merjet

 

[merjet]

You could also filter the data on Bound and copy the filtered data.

Hth,Merjet

 

[haas786]

You could also filter the data on Bound and copy the filtered data.

Hth,Merjet

Merjet...thanks for your help - the earlier programming code helped! I
have a new problem similar to this one and am re-posting it. I
appreciate all your help! Thanks..