Excel - Copying row from another sheet
Issue
I am working with 2 Excel, One field is common in both the excel "SID". I need to copy the row of SID say 'ABC' from Excel B and append to the row of same SID 'ABC' in excel A.
Please note I have 14 sheets in Excel B. The source where SID has to be searched and row needs to be fetched.
so I have 2 challenges in front of me:
- Pick one SID from Excel A and search it in 14 sheets of excel B
- After finding copy the corresponding row in excel B and append it in the picked SID in Excel A.
E.g.:
Excel A SID Message Number Order date ABC 43556 23/05/2009
Excel B Sheet 3 <just for example> SID Total days SDT XYZ 12 45 ABC 21 32
Result should be in Excel A :
ABC 43556 23/05/2009 21 32
Solution
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.
Macro 1:
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
Macro 2:
Sub undo()
With Workbooks("excel A.xls").Worksheets("sheet1")
Range(.Range("d1"), .Range("d1").End(xlToRight)).EntireColumn.Delete
End With
End Sub
Note
Thanks to venkat1926 for this tip on the forum.
Related
- Excel - Copying row from another sheet
- Excel copy row to another sheet based on cell value ✓ - Forum - Excel
- Excel move row to another sheet based on cell value ✓ - Forum - Excel
- Transfer Data From One Excel Sheet to Another - How-To - Excel
- Excel copying data from one sheet to another - How-To - Excel
- Excel copy row to another sheet if -vba ✓ - Forum - Excel
This document, titled « Excel - Copying row from another sheet », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
Subscribe To Our Newsletter!
