Hi Tom,

I can't get the macro to work and I'm wondering if it is because Col A is
text, Col B is currency and Col D is a date? Also I added the location of
the new worksheet which is located on my C drive so here is what I've done:
Can you see what I'm doing wrong? Thanks for your help.

Sub copydata()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim rng3 As Range
With Worksheets("Data Input")
Set rng = .Range("B19:57").SpecialCells(xlConstants, xlTextValues)
If Not rng Is Nothing Then
Set rng1 = Intersect(rng.EntireRow, .Columns(1).Resize(, 2))
Set rng2 = Intersect(rng.EntireRow, .Columns(4))
Else
Exit Sub
End If
End With
Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce
Smith\0-Production File\Balancing\Pending and Short Log\Pending and
Short.xls") _
.Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2)
rng1.Copy rng3.Offset(0, -1)
rng2.Copy rng3.Offset(0, 2)

End Sub



"Tom Ogilvy" wrote:

assumes the text values in column B are constants and not produced by
formulas.


Sub copydata()
Dim rng as Range, rng1 as Range, rng2 as Range
Dim rng3 as Range
with worksheets("Data Input")
set rng = .Range("B19:57").SpecialCells(xlConstants,xlTextVa lues)
if not rng is nothing then
set rng1 = Intersect(rng.entireRow,.Columns(1).Resize(,2))
set rng2 = Intersect(rng.entireRow,.Columns(4))
else
exit sub
end if
end with
set rng3 = workbooks("Pending and Short.xls") _
.Worksheets("Pending").Cells(rows.count,2).End(xlu p)(2)
rng1.copy rng3.offset(0,-1)
rng2.copy rng3.offset(0,2)

--
Regards,
Tom Ogilvy



"SITCFanTN" wrote:

I'm not familiar with if then statements, although I've heard of them so I'm
thinking that is what I need for this situation.

What I need to do is copy the text from the active sheet called "Data
Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if
there is text in column B and copy it to a new workbook titled "Pending and
Short", to sheet called "Pending". It would copied to the next available row
where Col B is empty.
I know I need to concatenate the column text into Col B and not sure of
that either. Thanks for your help in advance.