0

I am new to creating macros. I have tried searching for existing macros to help with my current request; however I have not found a helpful one. Please let me know if you can assist.

Contents

  1. 'Master Workbook.xlsm' contains:
    • 'Worksheet A': (A3:Last Line) is a list of names
    • 'Worksheet B': (A3:Last Line) is Blank and will be populated with the exported data from 'Supportingworkbook 1' and 'Supportingworkbook 2'
  2. 'Supportingworkbook1.xlsx'
    • Worksheet1: Contains data
  3. 'Supportingworkbook2.xlsx'
    • Worksheet1: Contains data

Requesting Assistance: To Create a macro button that will “quickly run in the background” to do the following.

Using the data listed in 'Mastetrworkbook.xlsm', 'WorksheetA', [A3:Last Row, B3:Last Row, and C3:Last Row ], search and filter* through 'Supportingworkbook1.xlsx' (‘Worksheet1’), and 'Supportingworkbook2.xlsx', (‘Worksheet1’), [D46:Last Row, I46:Last Row, and J46:Last Row] for matches. ****The filter must contain at least 1 of the 3 listed/provided data from ‘WorksheetA’ per each row.***

When Matches are found, copy and export the specified cells of data to the 'Mastetrworkbook.xlsm', ‘WorksheetB’.

Example: When the name Michele Jackson is filtered and found in 'Supportingworkbook1.xlsx' at row D:589 AND/OR also filtered and found in 'Supportingworkbook2.xlsx', in row D:146. Copy the rows corresponding values from column D, Column I, Column J, and Columns L: “2nd to Last Column”. Paste the copied values into 'Mastetrworkbook.xlsm', 'WorksheetB', (Starting at A:3). Therefore it will look like this.

Copy 'Supportingworkbook1.xlsx' Value from D:589 -> Paste into 'Mastetrworkbook.xlsm' Column C:3

Copy 'Supportingworkbook1.xlsx' Value from Column I:589 -> Paste into 'Mastetrworkbook.xlsm' Column B:3

Copy 'Supportingworkbook1.xlsx' Value from Column J:589 -> Paste into 'Mastetrworkbook.xlsm' Column A:3

Copy 'Supportingworkbook1.xlsx' Value from Columns L:589 trough “2nd to Last Column”:589 -> Paste into 'Mastetrworkbook.xlsm' Columns E:3 through HB:3.

Copy 'Supportingworkbook2.xlsx' Value from D:146 -> Paste into 'Mastetrworkbook.xlsm' Column C:3

Copy 'Supportingworkbook2.xlsx' Value from Column I:146 -> Paste into 'Mastetrworkbook.xlsm' Column B:3

Copy 'Supportingworkbook2.xlsx' Value from Column J:146 -> Paste into 'Mastetrworkbook.xlsm' Column A:3

Copy 'Supportingworkbook2.xlsx' Value from Columns L:146 trough “2nd to Last Column”:146 -> Paste into 'Mastetrworkbook.xlsm' Columns E:3 through HB:3.

Any and ALL help would be greatly appreciated. Thanks!!!

  • This would be easier in Access. Import the excel worksheets into separate tables I Access. Create a query that matches the names in each row and run it. – wbeard52 Jul 29 '13 at 2:48
  • Hi, thanks for your response. However, the 'SupportingWorkbook.xlsx' documents are generated weekly in excel format. I am trying to use the originated document verses having to export to access then starting the filter, search export from an Access file. Thanks, though. – MacroHelp911 Jul 29 '13 at 6:41
  • Take a look at recording a macro, doing some stuff, stop recording and look at it in the vba editor. This will give you an idea of where to start – Raystafarian Jul 29 '13 at 9:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.