Copy to next empty row, if not a match
 

I have the following code to copy data from one sheet to another:

Sheets("CallerInput").Range("B29:BQ29").Copy _
Sheets("InfoLoader").Range("B" & LastRow(Sheets("InfoLoader")) + 1)

However, cell B29 in the range to be copied is a date. How can I amend
this code to first look down column B of the paste target in
"InfoLoader". It would then overtype any day already held, or otherwise
paste to the end?

Thanks in advance

Steve

You can use application.match() to see if there's a date that matches:

Option Explicit
Sub testme()

Dim infoWks As Worksheet
Dim callWks As Worksheet
Dim res As Variant
Dim myRng As Range
Dim DestCell As Range

Set infoWks = Worksheets("infoloader")
Set callWks = Worksheets("callerinput")

Set myRng = infoWks.Range("b:b")

'sometimes converting to a long will make the match work better with dates.
res = Application.Match(CLng(callWks.Range("b29").Value) , myRng, 0)

If IsError(res) Then
Set DestCell = infoWks.Range("B" & LastRow(infoWks) + 1)
Else
Set DestCell = myRng(res)
End If

callWks.Range("b29:bq29").Copy _
Destination:=DestCell

End Sub

'my easy version of LastRow--for testing only
'use your own.
Function LastRow(wks As Worksheet) As Long
With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
End Function

Steve wrote:

I have the following code to copy data from one sheet to another:

Sheets("CallerInput").Range("B29:BQ29").Copy _
Sheets("InfoLoader").Range("B" & LastRow(Sheets("InfoLoader")) + 1)

However, cell B29 in the range to be copied is a date. How can I amend
this code to first look down column B of the paste target in
"InfoLoader". It would then overtype any day already held, or otherwise
paste to the end?

Thanks in advance

Steve

--

Dave Peterson

Dave, thanks a lot for this; I really appreciate it.

However, the range to be copied all contain references to cells
elsewhere on that sheet, eg: (B29) =A4, (C29) = B4 and so on. Is there
a way that this can copy just the *values* in the cells, not the
*formula*?

Thanks in advance

Steve

If you record a macro when you do a copy|paste special|values you'll see the
code.

But another way is to just assign the values:

Option Explicit
Sub testme()

Dim infoWks As Worksheet
Dim callWks As Worksheet
Dim myRng As Range
Dim res As Variant
Dim myFromRng As Range
Dim DestCell As Range

Set infoWks = Worksheets("infoloader")
Set callWks = Worksheets("callerinput")

Set myRng = infoWks.Range("b:b")

'sometimes converting to a long will make the match work better with dates.
res = Application.Match(CLng(callWks.Range("b29").Value) , myRng, 0)

If IsError(res) Then
Set DestCell = infoWks.Range("B" & LastRow(infoWks) + 1)
Else
Set DestCell = myRng(res)
End If

Set myFromRng = callWks.Range("b29:bq29")

DestCell.Resize(myFromRng.Rows.Count, myFromRng.Columns.Count).Value _
= myFromRng.Value

End Sub


Steve wrote:

Dave, thanks a lot for this; I really appreciate it.

However, the range to be copied all contain references to cells
elsewhere on that sheet, eg: (B29) =A4, (C29) = B4 and so on. Is there
a way that this can copy just the *values* in the cells, not the
*formula*?

Thanks in advance

Steve

--

Dave Peterson

That's done it. Thanks very much.

Steve