If then statement

[Guest]

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.

 

[Guest]

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,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("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)

 

[Guest]

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]

you can't copy to a closed workbook


instead of

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)

try

Dim bk as Workbook
set bk = Workbooks.Open(C:\Documents and Settings\" & _
"jsmith\Desktop\Joyce Smith\0-Production File" & _
"\Balancing\Pending and Short Log\Pending and Short.xls")
set rng3 = bk.Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2)

 

[Guest]

Hi Tom,

I"ve replaced the text that you mentioned and open the workbook first but
now I get a 1004 run time error. I thought I got the error because on rows
32 and 45 the cells A-D were mergered so I changed the criteria to 20 - 31
and it still doesn't run through. I'm not sure what is wrong. Knowing that
I have a few merged cells, I need to run for ranges ("20:31"), ("33:44") and
("46:57"). Why would I be getting that error and how would I code for the 3
sets of ranges.

Thanks again for your help.

Set rng = .Range("B20:31").SpecialCells(xlConstants, xlTextValues)




Sub CopyDataToPendAndShortLog()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim rng3 As Range
With Worksheets("Data Input")
Set rng = .Range("B20:31").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
Dim bk As Workbook
Set rng3 = Workbooks("C:\Documents and Settings\Joyce Smith\Desktop\Joyce
Smith\0-Production File\Balancing\Assets Daily Balancing\Pending and Short
Log.xls").Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2)
rng1.Copy rng3.Offset(0, -1)
rng2.Copy rng3.Offset(0, 2)

End Sub