Macro SoS!

[bobby]

Hi,

how to use Macro to automate the work as:
1. recognize the name and copy its value, then paste to second sheet where has the same name
2. repeat the work for each coloum
1214012139
As you can see I copy the data from first sheet and then paste to second sheet my code is

=IFERROR(INDEX($C$11, Match(Sheet1!$B$11,Sheet2! A5,0))," ")
It works..

I just cant put it into macro

Some one please give me some hint,,, Thanks

[Bob Phillips]

I would do it with formulas, but


Public Sub CopyValues()
Dim rowTotals As Long
Dim colLast As Long
Dim rowMatch As Long
Dim i As Long

With Worksheets("Copy")

colLast = .Cells(3, .Columns.Count).End(xlToLeft).Column
For i = 1 To colLast

If .Cells(5, i).Value <> "" Then

rowMatch = 0
On Error Resume Next
rowMatch = Application.Match(.Cells(5, i).Value, Worksheets("Paste").Columns(1), 0)
On Error GoTo 0
If rowMatch > 0 Then

Worksheets("Paste").Cells(rowMatch, "D").Value = .Cells(11, i + 1).Value
Worksheets("Paste").Cells(rowMatch, "F").Value = .Cells(11, i + 1).Value - .Cells(15, i + 1).Value
End If
End If
Next i
End With
End Sub

[bobby]

Thanks alot for the reply! Are you using loop in this case?

[Bob Phillips]

Can't you see that I am, a For ... Next loop.

[bobby]

Sorrry i know nothing about VBA... my apology...

[bobby]

Thanks xld. I just ran your Code. It works. But I forgot to mention that each invoice has to only contain one type of bill.. If i want to copy paste administration Fees to invoice form, then i have to use another empty sheet... How can i do that? Thank you Xld

[Bob Phillips]

Sorry, I am not sure what that means.

[bobby]

:hi:Its ok. I think i just did not explain to you well.. I have one template ( for invoice preparation ). Everyday i receive lots of Bills ( Consulting fees, Legal fess etc) I have to prepare invoice form for each bill. Since i m fed up with manual work, I want to run a macro to do the job for me. But first I need to let macro know which column i want to extract the data from. Then Paste to the invoice form wherever row it belongs ... i dont know if its clearer or even worse.. ^^ THanks Xld,,