hey Cguan,

thank you for the reply. the macro works but copies the entire row to sheet2.

nearly there but not quite. two more things
1) if i need to look for a whole column instead of y2 = "0014" for example y2 = A:A in sheet 2
2)I need to copy the value in Sheet1 Column C only not the entire row.

thanks

try this, hope it helps.

hey Cguan,

thanks for getting back to me.

after a long research i have found the following macro which copies data based on a condition and runs a loop as well.

Sub CopyData()
Dim lRow As Long, x As Long, y As Long, z As Long

lRow = Sheets("sheet1").range("A65535").End(xlUp).Row

y = 2

For x = 2 To lRow
If Sheets("sheet1").range("B" & x) = "700" Then
Sheets("sheet2").range("B" & y).Value = Sheets("sheet1").range("C" & x).Value

y = y + 1
End If
Next
End Sub


one problem remains.
i would like the macro to past value in front of the corresponding client. as from my previous example client "0014" has a "700" code. this macro picks up all the balances which are "700" but it does not match them with the right "client". any idea how i can do that. as both sheets have client codes in Column A.

thank you very much.

shah

try this:

thanks man,

we are nearly there. the macro works for one client but it does not pick the balances for remaining clients. lets say 0014 is the first client and 0027A is the 2nd client and 0027A2 is the third client.
the macro stops at first client (0014) and pastes the value.
i want it to keep looking in sheet2 column A and get the balances from sheet1 column C until there are no more clients in Sheet2 column A.

thanks

check my previous post..i implement the input box..then just put a loop.. like msgbox yes, no..

Hi Cguan,

I have a similar request to build a macro for the spreadsheet to pull data from the master sheet with a few parameters in particular. If you can help me, I will send you the sample data.

Looking forward for your reply.

-Ragsy

ok, try sending your data...maybe guys around here also got an idea on what you want to achieve...

and also post details on what you want to do

Welcome to DaniWeb!!!

Hello everyone,

I am posting a Vlookup Macro. thanks to Brian Baulsom. I hope this will help everyone. If you have any questions please let me know.


'=========================================================
'- GENERIC LOOKUP MACRO TO
'- FIND A VALUE IN ANOTHER WORKSHEET
'- AND RETURN A VALUE FROM ANOTHER COLUMN
'=========================================================
'- select the cell containing the first search value
'- and run this macro from there.
'- can be set to continue down the column
'- [** need to make changes below as required **]
'- Brian Baulsom May 2005
'==========================================================
Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object

'=============================================================
'- MAIN ROUTINE
'=============================================================
Sub DO_LOOKUP()
Application.Calculation = xlCalculationManual
'----------------------------------------------------------
'- LOOKUP SHEET [**AMEND AS REQUIRED**]
Set FromSheet = Sheets("S6values")
LookupColumn = 1 ' look for match here
FromColumn = 2 ' return value from here
'-----------------------------------------------------------
'- ACTIVE SHEET
Set ToSheet = Sheets("MonthlyFigures")
ActiveColumn = 1
StartRow = 6
'-------------------------------------------------------------
'- COMMENT OUT UNWANTED LINE, UNCOMMENT THE OTHER
'- ..............................[** FOR MULTIPLE ROWS **]
'LastRow = ToSheet.Cells(65536, ActiveColumn).End(xlUp).Row
'-
'- ..............................[** FOR A SINGLE VALUE **]
LastRow = ToSheet.Cells(65536, ActiveColumn).End(xlUp).Row
'-------------------------------------------------------------
'- COLUMN NUMBER TO PUT RETURNED VALUE [**AMEND AS REQUIRED**]
ReturnColumnNumber = 2 ' column number
'-------------------------------------------------------------
'- loop through each row (which may be only 1)
For ToRow = StartRow To LastRow
MyValue = ToSheet.Cells(ToRow, ActiveColumn).Value
FindValue
Next

End Sub
'== END OF PROCEDURE ====================================================

'========================================================================
'- FIND VALUE
'========================================================================
Private Sub FindValue()
Set FoundCell = _
FromSheet.Columns(LookupColumn).Find(MyValue, LookIn:=xlValues)
If FoundCell Is Nothing Then

Else
FromRow = FoundCell.Row
'- transfer additional data.
ToSheet.Cells(ToRow, ReturnColumnNumber).Value = _
FromSheet.Cells(FromRow, FromColumn).Value
End If
End Sub
'=========================================================================******************************************************