Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
That's done it. Thanks very much.
Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and pasting graphs to PowerPoint | Excel Discussion (Misc queries) | |||
CountIF cells are not empty | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) | |||
copy paste cell character limit | Excel Discussion (Misc queries) |