Copying cells

[Aidan]

Basically what I want to do is the following

On one worksheet I have the following:

Heading A

A 1
b 2
C 5
D 2

On another worksheet I have the heading Heading A and four lines underneath. I want to copy column one if column two is 2 or under.

I want the above example copied as:

A
B
D

With no space in between the b and the D

There are two many IF's to do an IF statement, so can anyone help?

 

[erik.van.geit]

Hi, Aidin,

you can put a formula next to those columns
(in the code the formula is written in an inserted column)
then apply autofilter to exclude blanks
copy and paste

this all happens in this code
step through it with F8 to see what it is doing

kind regards,
Erik

Sub Macro1()
Dim rng As Range
Set rng = Range(Cells(2, 1), Cells(65536, 1).End(xlUp))

Application.ScreenUpdating = False
Columns(1).Insert

With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(RC[2]<=2,RC[1],"""")"
.AutoFilter Field:=1, Criteria1:="<>"
.Copy Worksheets(2).Range("A2")
.AutoFilter
End With

Columns(1).Delete
Application.ScreenUpdating = True
End Sub

 

[Aladin Akyurek]

By means of a formula system...

http://www.mrexcel.com/board2/viewtopic.php?t=129601

where you just need to adapt the condition part of the IF formula...

 

[Aidan]

Hi Eric,

I clicked a wrong button so hope that I didn't report you (which I didn't mean to do).

Can you explain where I would put that code and how it would all work. I have done some macro stuff before but I do not really have too much knowledge of code.

Regards

Aidan

I will be offline from now until tomorrow morning but if you understand what I am trying to say then that would be excellent.

 

[erik.van.geit]

Look also carefully to Aladins formula-system!

here is my "how to ..."
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the code from my post

How to learn with example-codes...
1. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)
2. "step" through it with the function key F8, while observing what's going on on your sheet
3. click on an item and hit F1 to let popup the help
4. add some steps to see what's going on in your code
example
add: MsgBox "you're currently on this sheet :" & ActiveSheet.Name & Chr(10) & Cell(i, j).Address & "will be filled with the value" & tmp
or before running click in the line and hit F9 (inserting a breakpoint)

kind regards,
Erik

 

[Zack Barresse]

Hi Eric,

I clicked a wrong button so hope that I didn't report you (which I didn't mean to do).

You did.

But you answered my question. Take care fellas!