PDA

View Full Version : Copy Range Based on Condition



cambralenta
03-28-2013, 04:09 PM
I have recorded a macro that i need to use in sheet1:


Range("AZ1:AZ58").Select
Selection.Copy
Columns("AI:AI").Select
Range("AI1").Activate
Selection.Insert Shift:=xlToRight
Range("Q1:Q58").Select
Selection.Copy
Columns("AJ:AJ").Select
Range("AJ1").Activate
Selection.Insert Shift:=xlToRight


What I need is to change the code and copy from AZ column only if colum AG as values in it!


AG AI AJ AK AZ
xa 1
xb 2
3
4
5

The result should be:
AG AI AJ AK AZ
xa 1 Q1 1
xb 2 Q2 2
xc 3 Q3 3
4
5
Q1:Q58 has fixed values, but AG1:AG58 donīt (dynamic).
Iīm really new to VBA so I hope that some one can help me! Thanks... :bow:

mdmackillop
03-28-2013, 04:26 PM
Sub ConditionalCopy()
Dim r As Range, cel As Range
Set r = Range("AZ:AZ").SpecialCells(xlCellTypeConstants)
For Each cel In r
If cel.Offset(, -19) <> "" Then cel.Copy cel.Offset(, -17)
Next
End Sub

cambralenta
03-28-2013, 05:33 PM
Thank you for the code! Iīm almost there... I changed cel.Copy cel.Offset(, -17) to cel.Copy cel.Offset(, -16) because, donīt know why, it dinīt work on left column!

This is how it is now:
Dim r As Range, cel As Range
Set r = Range("AZ:AZ").SpecialCells(xlCellTypeConstants)
For Each cel In r
If cel.Offset(, -51) <> "" Then cel.Copy cel.Offset(, -16)
Next
Range("Q1:Q58").Select
Selection.Copy
Columns("AJ:AL").Select
Range("AJ1").Activate
Selection.Insert Shift:=xlToRight


I want to use this in a print area macro
so when the job is done everything goes
to early places.
What can I do for that?
Thank you again!

mdmackillop
03-29-2013, 09:06 AM
I want to use this in a print area macro
so when the job is done everything goes
to early places.
I don't understand.