I have an Excel Macro which copies from a Source spreadsheet and pastes on the working copy. The Source spreadsheet is the result of a report gleaned from a non Microsoft database. Usually all works as expected.
The problem comes when one of the items in the working copy does not appear in the Source copy. (If no activity occured, instead of displaying a zero, that particular item is excluded from the output. Since the procedure is copying and pasting to a set Range, these instances cause a mismatch on the Master Sheet and incorrect data in the linked cells elsewhere in the Workbook.
I know there is a way to have the copy and paste take place depending on which row contains a keyword but cannot find any directions to it.
Basically, how can I program the macro to check Range("B30") for a keyword, copy the attendant data, find the keyword on the target sheet and paste on the attendant row
Thanks
Copying and pasting between spreadsheets
flobadob
Thanks for your reply. You got me off in the right direction. I'm not sure if the approach below resembles yours at all but it works. Since I have two columns of data (one is number of accurences and the other is related Dollar amount) I needed to have it copy both cells in one operation.
Here is what I came up with:
Sub Newprocedure()
Windows("Total Fx Deals.xls").Activate
Cells.Find(What:="Fwd Buy Settled to DDA ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -2).Activate
Dim r1 As range, r2 As range, myMultiAreaRange As range
Set r1 = ActiveCell
Set r2 = ActiveCell.Offset(0, 1)
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
ActiveCell.Copy
End Sub
That One Developer Guy
If nyone thinks to try this there are a couple of changes I discovered are needed.
1. Replace "ActiveCell.Copy" with "Selection.Copy".
2. If there is a chance any searched items will be missing, give a name to each segment and add the statement "On Error goto (Next segment's name). It will work well with these changes.
Jim
Frode Fiplingdal
I do just this type of processing in my work.
I use a copy of the sheet that is the master list for desired data, say, the leftmost column which has names of something for which you pull data.
You can iterate over the range of daily data and insert them into your working sheet (based on a copy of the master list I presume). In this fashion you don't run into the problem of data in the periodic report not being equal to the master list. You also avoid the problem of 'cross pasting' values. You match data for data.
I'm sure there are much better ways to accomplish this, but it's a Q&D method I use to good result.
-hth-