You are here:

Excel/Fill data in SECOND wb from data of FIRST wb

Advertisement


Question
QUESTION: I have 2 workbooks WB1 & WB2 for input & output respectively & both kept opened.

WB1: Range: A1:E1085476


Sheet1

A   B   C   D   E
1   Date   Name   N1   N2   N3
2   14-Sep-14   ABCD   128.25   138.55   140.29
3   14-Sep-14   ABMN   128.25   138.55   140.29
4   15-Sep-14   ABCD   128.25   138.55   140.29
5   15-Sep-14   ABMN   128.25   138.55   140.29
6   16-Sep-14   ABMN   128.25   138.55   140.29
7   16-Sep-14   XYZAD   128.25   138.55   140.29
8   17-Sep-14   XYZAD   128.25   138.55   140.29


In WB2:
Output required as:

Sheet1

A   B   C   D   E
1   Date   ABCD   N1   N2   N3
2   14-Sep-14   ABCD   128.25   138.55   140.29
3   15-Sep-14   ABCD   128.25   138.55   140.29

Example 2:

Sheet1

A   B   C   D   E
1   Date   ABMN   N1   N2   N3
2   14-Sep-14   ABMN   128.25   138.55   140.29
3   15-Sep-14   ABMN   128.25   138.55   140.29
4   16-Sep-14   ABMN   128.25   138.55   140.29

In B2, I would be manually punching 1 name which is contained in B column of WB1. IF matched;
ANSWER: All related data (related to B2) should appear in A2:A1085476; C2:C1085476; D2:D1085476 & E2:E1085476

VBA required for this.
I am using Excel 2007.
Thanks for the help in advance.

ANSWER: Hi Sandeep,

You don't need to create a macro for this. This requirement can be handled using Vlookup. In WB2, create a Vlookup formula in each of the columns to lookup the manually entered value and retreive the corresponding values from WB1.

Hope this helps,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: I need only vba solution & NOT vlookup since it is large number of columns.

ANSWER: Hi Sandeep,

In that case, I have a few questions with respect to uniqueness and logic of the query:

1. You first example have multiple instances of ABCD and ABMN but the result contains only all ABCD values. I'm assuming you punched in ABCD and system pulled all records containing ABCD
2. Is the macro supposed to work with some date range or across all data?
3. Is it possible to have multiple records containing same values? Is so, then, should the macro retreive all values or just the unique records.

Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Answer: 1. YES I punched ABCD and system pulled all records containing ABCD
2. macro supposed to work across all data
3.Is it possible to have multiple records containing same values? YES
Is so, then, should the macro retreive all values or just the unique records.  ALL Values

Answer
Hi Sandeep,

I've created the below macro to look for one value in first sheet's first column and copy all the matching rows to the second sheet.

This is a basic macro which will search the value entered in cell A1 of first sheet, it will search in the range A1 to A100 and if it finds a match, it will copy 4 columns from that row into the second sheet.

You will need to customise the macro to your requirements.

Sub Button1_Click()
inputvalue = Worksheets("Sheet1").Cells(1, 1).Value
Rowcount2 = 1
For RowCount = 5 To 100

If Worksheets("Sheet1").Cells(RowCount, 1) = inputvalue Then
Worksheets("Sheet2").Cells(Rowcount2, 1) = Worksheets("Sheet1").Cells(RowCount, 1).Value
Worksheets("Sheet2").Cells(Rowcount2, 2) = Worksheets("Sheet1").Cells(RowCount, 2).Value
Worksheets("Sheet2").Cells(Rowcount2, 3) = Worksheets("Sheet1").Cells(RowCount, 3).Value
Worksheets("Sheet2").Cells(Rowcount2, 4) = Worksheets("Sheet1").Cells(RowCount, 4).Value
Rowcount2 = Rowcount2 + 1
End If

Next RowCount

End Sub


Hope this helps,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.