if specific cell value in a column, copy Closed

Question

Hello everybody, 

I have a table in a worksheet that keeps daily financial records (workbook keeps whole month). Column A is a serial number for each transaction, column B is its description. They both start to receive values from row 6 and on. There are other columns, of no interest in this question. Column A receive its first value by hand (cell A6) and all other rows have a "+1" formula. Column B takes its value from a dropdown list.

What I need to do, and I cannot find a workable solution, is: whenever a specific value appears in column B (one of the 80 in the list), I want excel to automatically copy the value of column A in the same row (transaction's serial number), paste it as value (not formula) to the first available cell in column O (starting from O6) and activate next cell (starting from P6) in order to receive relative information, again from a dropdown list (already there). All collumns will never exceed row 300, but in the end of each day there are macros deleting empty rows and summing all transactions.

I am new at VBA, and I still don't understand everything. I know about the 'Private Sub Worksheet_Change(ByVal Target As Range)' solution, but I need every possible help for this random copy-paste and activation of cell in another column and row. Sorry, I don't have any relative code to send. Up to now I manually do the procedure, but mistakes are made and it is time consuming.

Thank you very much!

Configuration: Windows XP / Firefox 6.0.2

TrowaD · Answer

Hi Freshfoot,

When the value in column B is found in the list of 80 items then
Copy value in column A, paste to column O.
This is clear to me, but what do you want to do with column P?

I assumed that column O has a header or some value in row 5.

Anyway look at this code, adjust it, run it (make backup or save first):
Sub test()
Dim x As Integer
For Each cell In Range("B1:B300")
'x = 1 means your list of 80 items starts at row 1
x = 1
Do
'the number 8 stands for the column number of the list with 80 items
If cell.Value = Cells(x, 8).Value Then _
cell.Offset(0, -1).Copy Destination:=Range("O" & Rows.Count).End(xlUp).Offset(1, 0)
x = x + 1
'x = 81 means that row 81 is the last row of the list with 80 items
Loop Until x = 81
Next
End Sub
Post back your findings so we can tweak the code.

Best regards,
Trowa

If specific cell value in a column, copy

1 response

Similar discussions

Subscribe To Our Newsletter!