Macro that will transfer data into single workbook

**K_Rene** · 09-01-2016, 06:29 PM

I'm still very new to macros so I’m not sure if a macro can be done for this.

I have a primary workbook (Workbook A) with 30 columns with titles. Data from other workbooks needs to be imported into Workbook A.
The issue is, the columns from the various workbooks do not match the columns in Workbook A. For example the other spreadsheets have like 15 columns with titles that aren’t exactly the same as Workbook A’s titles.

Some of the data will match up to Workbook A but some of Workbook A’s columns will need to have data manually input into them.

Also, I need each new set of data that is imported to go to the next available empty row of Workbook A instead of copying over the existing data in the spreadsheet. And if the other spreadsheets have some rows without data can the macro skip those rows and go to the next row with data?

I’ve worked with macros that completely transpose a tab and I was able to work with a code that will just copy and paste the data straight into the other sheet exactly as it is, but this is much more complex as the data isn't going to the same corresponding cell in the workbook.

I've attached an Excel file that just gives an example of what I mean when I say that Workbook A's titles don't match up with the titles of the data I'm trying to import and some of it needs to be entered manually.

**dflak** · 09-02-2016, 12:00 PM

This is certainly doable, but it would help a lot to include a sample workbook A and sample workbook B with enough non-sensitive data to demonstrate the concept. It is good to have the "mapping" you've provided in this workbook, But I don't think people are going to take the time to set up the workbooks and populate them with data.

One other question. Do you want to run the macro from workbook A (which you might not want to do if someone gives you a new workbook A every day), or do you want a separate workbook the opens A then opens B and appends B's data into A?

**K_Rene** · 09-11-2016, 10:28 AM

Attached are some sample workbooks. There's a lot more data that is listed in the real version of the "Example" workbook, but I didn't think it necessary to create an exact replica.
Workbook A is the main workbook and a new Workbook A is only created about twice a year so it is fine if the the macro is run from Workbook A. The users would probably find it confusing if it wasn't.

Sorry for the delay in my response. I was bogged down in another project.
I appreciate any direction someone can give me.

Thanks in advance!

**xladept** · 09-11-2016, 11:51 AM

Example rows 7-9 are dupes of rows 1-3 - is that the situation?

Oops! Nevermind

But are the headers the same in both?

**dflak** · 09-12-2016, 09:33 AM

Here is my shot at it. I display the data you want in an Excel Table. Excel tables have a lot of benefits: http://www.utteraccess.com/wiki/inde...ables_in_Excel. The one I'm using here is that tables know how big they are, so it's easy to clear them out.

I included a control panel page where you can tell the program where the data file is located and what it is called. Enter the appropriate data in column B and you are good to go. No need to change any code. There are two places in the code where you have to change the page name in case you change the name from Workbook A to something else. These are marked with comments.

The Control Panel has two buttons: Copy Data and Clear Table. The Copy Data Button runs the program and appends the data it reads to whatever is in the table. The Clear Table button clears out all the information in the table. I don't know how often you will need to "start from scratch" but you can do it if you need to.