trouble with macro

[Guest]

I have two workbooks and am trying to copy the currency value (if entered) in
col B, in rows 19 - 57 only from the "Data Input" WS in WB "Daily Balance
Today" and copy those values to the "Pending" WS in the "Pending and Short"
WB into the next availabel cell in col C. This workbook is a running total
so I need to add the information to the bottom of the spreadsheet. If values
are copied, I need to copy the text from Col A and the date in Col D from the
Data Input WS and copy that text to Col B in the Pending WS on the same row
as the currency value. This code is not working for me and I'm not sure what
is wrong. Thanks in advance for your assistance.


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(, 4))
Set rng2 = Intersect(rng.EntireRow, .Columns(2))
Else
Exit Sub
End If
End With

Dim bk as Workbook
set bk = Workbooks.Open(C:\Pending and Short Log\Pending and Short.xls")
set rng3 = bk.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]

First, you use xlTextValues, but it sounds like you are looking for a
currency value (xlNumbers)? If that were corrected or is correct, then

you have .Range("B19:57")
which should be .Range("B19:B57") It that were corrected, then

right now it copies columns A to A and then copies B to D

It uses column B in the Pending WS to determine the next available column.

You say you want to copy A, D and I assume B (the currency value) to column
B of Pending - doesn't make much sense - 3 cells to 1.

Also, it assumes the Daily Balance Today.xls is the activeworkbook when it
is run and that Pending and Short.xls is closed.

Perhaps the fix is

Sub copydata()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim rng3 As Range

With Worksheets("Data Input")
Set rng = .Range("B19:B57").SpecialCells(xlConstants, xlNumbers)
if rng.count > 1 then
Msgbox "More than one row is found - exiting"
exit sub
End if
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 bk = Workbooks.Open(C:\Pending and Short Log\Pending and Short.xls")
set rng3 = bk.Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2)

rng1.Copy rng3.Offset(0, -1)
rng2.Copy rng3.Offset(0, 2)

End Sub

 

[Guest]

Hi Again Tom,

I'm looking to copy all rows that have a value in col B. The code below
keeps firing the message box. I will always have more than one row that I
need to copy to the new sheet so should I just delete this code? Thanks
again.

if rng.count > 1 then

 

[Tom Ogilvy]

You can delete it if wish.

 

[Guest]

Hi Tom, the data is moving to the correct WB and WS however the informatoin
is going to the wrong columns and not cancatinating. I've played around with
the code but just can't get it correct. I'd like to add the text "text"
between the copied data from Col A and Col D from the source WB to the
Pending and Short Log or WB when it copies to Col B. This will help me
understand the code I think. I appreciate your help, thanks

Source WB Pending and Short WB

Col A "to be keyed" Col D goes to Col B
Col B goes to Col C