Solved: Store values meeting certain criteria and paste them into other area

[danlu]

Hi,

I have a column (column A) which contains letters and where I want to detect all a and store these into malomrade. When all values in column A are evaluated and for these values which have meet the criteria (to be an "a") and hence are stored in malomrade, these values should be pasted in column A on the next sheet. They should be pasted after each other (thus without any blanks between them. For ex if there are five "a":s stored in malomrade these should be pasted into cell a1 to a5).
Though this won't happen, and I guess it has something to do with the evaluation, ie not only "a":s are stored into malomrade. Also something is probably not properly set up when it comes to specifying the area for pasting. All help or input appreciated.


[VBA] Sub test()

Dim omrade As Range
Dim malomrade As Range
Dim annatomrade As Range
'Dim catcharea As Range

Dim c As Range

Set omrade = Range("A1:A20")
For Each c In omrade
If c = "a" Then _
'If omrade Is Nothing Then Set omrade = c Else
Set malomrade = Application.Union(omrade, c)
End If
Next c


Set annatomrade = Worksheets(2).Cells(malomrade.Rows.Count, malomrade.Columns.Count)
malomrade.Copy Destination:=annatomrade

End Sub[/VBA]

Edited by Aussiebear: Danlu, please use the vba button to enclose the code you post to this forum. This function makes the code so much easier to read.

[Bob Phillips]

[vba]

Sub test()
Dim omrade As Range
Dim malomrade As Range
Dim annatomrade As Range
'Dim catcharea As Range

Dim c As Range

Set omrade = Range(Range("A1"), Range("A1").End(xlDown))

For Each c In omrade

If c = "a" Then

If malomrade Is Nothing Then

Set malomrade = c
Else

Set malomrade = Application.Union(malomrade, c)
End If
End If
Next c

Set annatomrade = Worksheets(2).Range("A1")
malomrade.Copy Destination:=annatomrade

End Sub
[/vba]

[danlu]

Hi,

It works. Great. Though, how come that it is enough to assign one cell (as is done with: Set annatomrade = Worksheets(2).Range("A1") ) as a target range where the stored values (all "a":s) in malomrade, holds more than one value, and hence will account for more than one cell as target range?
I tried to create a target range whose area was supposed to correspond to the number of values stored in the variable malomrade, but this doesn't seem to be necessary?


[VBA] Sub test()
Dim omrade As Range
Dim malomrade As Range
Dim annatomrade As Range
'Dim catcharea As Range

Dim c As Range

Set omrade = Range(Range("A1"), Range("A1").End(xlDown))

For Each c In omrade

If c = "a" Then

If malomrade Is Nothing Then

Set malomrade = c
Else

Set malomrade = Application.Union(malomrade, c)
End If
End If
Next c

Set annatomrade = Worksheets(2).Range("A1")
malomrade.Copy Destination:=annatomrade

End Sub[/VBA]

Edited by Aussiebear: VBA button definately works..... if you use it.

[mdmackillop]

Hi Danlu,
Please use the VBA button to format your posted code.

[danlu]

The reason behind my though was actually to go on (after fixing the initial problems which you provided a solution for) and add an additional step to the code which includes a check that the area to be used as the target range is actually a cleared area, and if not, have some sort of error message saying that data cannot be pasted there since the area already contains data. To do this I guess you have to know how big the area will be before you perform the paste operation.
Comments to my question in the previous post and in this would be of great value to me.

[Bob Phillips]

Hi,

It works. Great. Though, how come that it is enough to assign one cell (as is done with: Set annatomrade = Worksheets(2).Range("A1") ) as a target range where the stored values (all "a":s) in malomrade, holds more than one value, and hence will account for more than one cell as target range?
I tried to create a target range whose area was supposed to correspond to the number of values stored in the variable malomrade, but this doesn't seem to be necessary?

You only need to tell it the first cell to paste into, VBA can work out how big the source is.

[Bob Phillips]

The reason behind my though was actually to go on (after fixing the initial problems which you provided a solution for) and add an additional step to the code which includes a check that the area to be used as the target range is actually a cleared area, and if not, have some sort of error message saying that data cannot be pasted there since the area already contains data. To do this I guess you have to know how big the area will be before you perform the paste operation.
Comments to my question in the previous post and in this would be of great value to me.

I think this would do what you want

[vba]

Set annatomrade = Worksheets(2).Range("A1")
If Application.CountA(annatomrade.Resize(malomrade.Rows.Count, malomrade.Columns.Count)) > 0 Then

MsgBox "Target area is not blank"
Else

malomrade.Copy Destination:=annatomrade
End If
[/vba]

[danlu]

Your code to do the extra check worked perfectly. Thanks a lot.

[hzelstarfish]

So this is the same concept as this solved problem with one slight alteration. I would like to know how to copy the whole row that the certain value is in and paste it to the new sheet. Also, is there a way to make entering the value trigger the macro as opposed to having to run the macro manually? Thanks!!!!

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

.EntireRow.Copy Worksheets("Sheet2").Cells(.Row, "A")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[hzelstarfish]

So, I know this should be easy, but I’ve managed to retain nothing that I once knew about VBA in Excel. Can you help me apply this to my specific situation? What I have is a workbook with 2 sheets. One is called “Events” and the other is called “Misoperations”. The user initially enters data on the “Events” sheet. What I want is for the macro to be triggered if the user selects “Y” from the dropdown list in column I on “Events”. The macro needs to copy columns A-H of the active row in “Events” and paste it to columns A-H in the next available row in “Misoperations”. Thank you so much! Your help is GREATLY appreciated!!!!!

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I:I" '<== change to suit
Dim NextRow As Long
Dim wsMisop As Worksheet

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value = "Y" Then

Set wsMisop = Worksheets("Misoperations")
If wsMisop.Range("A1").Value = "" Then

NextRow = 1
ElseIf wsMisop.Range("A2").Value = "" Then

NextRow = 2
Else

NextRow = wsMisop.Range("A1").End(xlDown).Row + 1
End If

Me.Cells(.Row, "A").Resize(, 8).Copy wsMisop.Cells(NextRow, "A")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[hzelstarfish]

THANK YOU!!!!!!