Match and copy values from one workbook to another

[TheAnswer]

I have this report which shows the amount of work done by each department(column) for each month(row). I want to copy the values from this workbook to another but they are in different format. Is there a way to copy by refering to the department and month and paste it on a specific cell of another worksheet.

Sorry, i am new thus need your help.
Appreciate all helps.


[vba]Private Sub cmdCopy_Click()
Workbooks.Open Filename:="C:\Documents and Settings\A999416\Desktop\Project\bbca volume report 2010_may_team.xls"
Workbooks("bbca_mgt_report_wip.xls").Activate
Sheets("Vol").Activate
If tean = "Agency & Custodian" & Txn = "Deposit Placement/Rollover" & month(Date) = 10 / 2010 Then
Cells.Copy
Workbooks("bbca volume report 2010_may_team.xls").Activate
Sheets("Agency 2010").Activate
Worksheets("Agency 2010").Range("M43:M45").PasteSpecial xlPasteValues
ActiveWorkbook.Save
End If
End Sub[/vba]
I tried this coding but it cant seem to work.

[Bob Phillips]

What is this line doing, meant to do?

[vba]

If tean = "Agency & Custodian" & Txn = "Deposit Placement/Rollover" & month(Date) = 10 / 2010 Then
[/vba]

[TheAnswer]

I want the system to look through the workbook and if "agency & custodian" is found under the tean catergory, "deposit Placement/rollover" under the txn category and "10/2010" under the month category then the system will copy the cell and paste it onto another workbook.

[Bob Phillips]

I would suggest that you use Find to look at the tean column and where the other conditions are met copy it over, then do FindNext.

[TheAnswer]

Is it possible for you to write a sample coding for me cause i am new to VBA and do not really know about the codings?

I will really appreciate your help if you can do that.

[Bob Phillips]

VBA Help has a very clear example on Find and FindNext. Take a pop at it and post back with your attempt if you have any problems.

[TheAnswer]

[vba]On Error Resume Next
With Sheets("Vol")
Set aFound = .Row(10).Find(What:="Agency & Custodian", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
With Sheets("Vol")
Set bFound = .Row(9).Find(What:="10/2010", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
On Error Resume Next
With Sheets("Vol")
Set cFound = .Column(C).Find(What:="Deposit Placement/Rollover", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
With aFoundCell And bFoundCell And cFoundCell
Cells(cFound, bFound).Copy
End With
[/vba]
I tried this but it cant seem to work. There is no syntax error but nothing is copied.

[TheAnswer]

VBA Help has a very clear example on Find and FindNext. Take a pop at it and post back with your attempt if you have any problems.

[vba]On Error Resume Next
With Sheets("Vol")
Set aFound = .Row(10).Find(What:="Agency & Custodian", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
With Sheets("Vol")
Set bFound = .Row(9).Find(What:="10/2010", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
On Error Resume Next
With Sheets("Vol")
Set cFound = .Column(C).Find(What:="Deposit Placement/Rollover", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
With aFoundCell And bFoundCell And cFoundCell
Cells(cFound, bFound).Copy
End With
[/vba]
I tried this but it cant seem to work. There is no syntax error but nothing is copied.

[GTO]

...I tried this but it cant seem to work. There is no syntax error but nothing is copied.

Hi there,

I am afraid there are syntax errors aplenty... You are masking them by using On Error Resume Next. Take these out, and you can step through the code and start finding the errors.

[vba]Sub sample()
Dim aFound As Range, bFound As Range, cFound As Range
'On Error Resume Next
With Sheets("Vol")
'Row() should be Rows(10)... ".Cells() belongs to the sheet, so .Cells(1,1)
' would refer to cell A1, which will error, as
' we're only looking in row 10.
Set aFound = .Rows(10).Find(What:="Agency & Custodian", After:=.Rows(10).Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With

With Sheets("Vol")
Set bFound = .Rows(9).Find(What:="10/2010", After:=.Rows(9).Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With

'On Error Resume Next
With Sheets("Vol")

'C w/o quotes would be a variable, you want the string "C"
'Same as above, you want Columns, not Column
Set cFound = .Columns("C").Find(What:="Deposit Placement/Rollover", _
After:=.Columns("C").Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With

' aFoundCell, bFoundCell, cFoundCell do not exist, leastwise not in the code you provided. aFound, ect are
' the ranges we tried to set references to above.
' With aFoundCell And bFoundCell And cFoundCell
' Cells(cFound, bFound).Copy
' End With
End Sub[/vba]

Hope that helps a little at least,

Mark

PS - when posting code, if you use the [vba]...your code here...[/vba] it lays out the code nicely and makes it easier to read.

[TheAnswer]

Hi, thanks

"Deposit Placement rollover" is sub category of "Agency & Custodian" . Is there a method to copy the cell which is under "Deposit Placement/ rollover" and "Agency & Custodian" and paste it onto another workbook which has the same name[("Agency & custodian) and ("Deposit Placement/Rollover")?

Sorry for my lousy english structure.
Hope you understand what i am trying to explain.

[GTO]

Hi, thanks

...and paste it onto another workbook which has the same name[("Agency & custodian) and ("Deposit Placement/Rollover")?

Sorry for my lousy english structure.
Hope you understand what i am trying to explain.

It may well just be me being a bit thick-headed, but I am not understanding what that means exactly.

Could you attach a small sample workbook? You could use to sheets to demonstrate the source and destination sheets.

Mark

[TheAnswer]

This is sample 1

[TheAnswer]

This is sample 2. I am not sure how to upload two files at a time. Sorry for the inconvinence.

Anyway i wish to copy the "1 2 3" over from sample 1 to sample 2. Hope it is clearer now.

[TheAnswer]

You might want to refer this sample2 instead. Thanks