Copy with lookup
Solved/Closed
Shankar
-
Mar 22, 2010 at 12:43 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 22, 2010 at 10:02 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 22, 2010 at 10:02 PM
Related:
- Copy with lookup
- Area code lookup - Guide
- Https //accounts.google.com/sign in/v1/lookup - Guide
- India license plate lookup - Guide
- Ip address lookup android - Guide
- T mobile sim card lookup - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 22, 2010 at 10:02 PM
Mar 22, 2010 at 10:02 PM
shankar
FOLLOW CAREFULLY
I am giving you a macro "test" for this purpose. in the sample workbook excel A(remember there is space between "excel"and "A") sheet1 where you have the main data add one more sample data in the third from A3 to right like this
jkh 23456 5/30/2009
both the SAMPLE workbooks must have been saved and open
then try the macro (I have given another macro "undo" which undoes the result of the macro "test"
If there is problem tell me which code statement gives the problem and error message if any
Park the macros in the vb editor of excel A (thought strictly it does not matter)
test the macros in the sample workbooks and if it is successful use the macro in your original file . BEFORE DOING THAT KEEP THE ORIGNAL FILES SAFELY SOMEWHERE SO THAT THE FILES CAN BE RETRIEVED IF THERE IS A MESS UP.
the macro are
FOLLOW CAREFULLY
I am giving you a macro "test" for this purpose. in the sample workbook excel A(remember there is space between "excel"and "A") sheet1 where you have the main data add one more sample data in the third from A3 to right like this
jkh 23456 5/30/2009
both the SAMPLE workbooks must have been saved and open
then try the macro (I have given another macro "undo" which undoes the result of the macro "test"
If there is problem tell me which code statement gives the problem and error message if any
Park the macros in the vb editor of excel A (thought strictly it does not matter)
test the macros in the sample workbooks and if it is successful use the macro in your original file . BEFORE DOING THAT KEEP THE ORIGNAL FILES SAFELY SOMEWHERE SO THAT THE FILES CAN BE RETRIEVED IF THERE IS A MESS UP.
the macro are
Sub test() Dim r As Range, c As Range Dim x As String, j As Integer, k As Integer Dim cfind As Range, r1 As Range With Workbooks("excel A.xls").Worksheets("sheet1") Set r = Range(.Range("A2"), .Range("A2").End(xlDown)) For Each c In r x = c.Value With Workbooks("excel B.xls") j = .Worksheets.Count For k = 1 To j With .Worksheets(k) Set cfind = .Cells.Find(what:=x, lookat:=xlWhole) If Not cfind Is Nothing Then Set r1 = Range(cfind.Offset(0, 1), cfind.End(xlToRight)) r1.Copy GoTo pasting End If End With 'worksheets(k) Next k Exit Sub End With 'second book pasting: c.Offset(0, 3).PasteSpecial Next c End With 'first book End Sub
Sub undo() With Workbooks("excel A.xls").Worksheets("sheet1") Range(.Range("d1"), .Range("d1").End(xlToRight)).EntireColumn.Delete End With End Sub