I have a workbook that I've been extensively automating but unfortunately I'm
still pretty new to VBA. I have a long range of data that I'd like to copy to
a new sheet *only when* a particular column contains data. For example, my
data range starts in cell B35 of Sheet1 and goes to L301. I'd like to copy
only those rows of data when a value has been placed in column E (column E
cells are blank by default) to Sheet2. I'd also like the information to port
over as values only. Can anyone help me out? Thanks in advance for any and
all help!!

On Fri, 31 Jul 2009 01:24:02 -0700, tpmax
wrote:

I have a workbook that I've been extensively automating but unfortunately I'm
still pretty new to VBA. I have a long range of data that I'd like to copy to
a new sheet *only when* a particular column contains data. For example, my
data range starts in cell B35 of Sheet1 and goes to L301. I'd like to copy
only those rows of data when a value has been placed in column E (column E
cells are blank by default) to Sheet2. I'd also like the information to port
over as values only. Can anyone help me out? Thanks in advance for any and
all help!!


Try this macro:

Sub copy_conditionally()
Set my_destination = Worksheets("Sheet6").Range("B35")
' this is the upper left corner of the destination
row_offset = 0
For Each c In Range("E35:E301")
If c < "" Then
c.Resize(1, 11).Offset(0, -3).Copy
my_destination.Offset(row_offset, 0).PasteSpecial (xlPasteValues)
row_offset = row_offset + 1
End If
Next c
Application.CutCopyMode = False
End Sub

You may want to clear the target sheet in the beginning of the macro.
If you want blank rows (gaps) in the destination where the E column is
blank you can move the increase of row_offset outside the If - Then.

Hope this helps / Lars-Åke