Merging and Formatting 3 Excel Files

[sparky667]

I have three files I need to merge:
Template.xlsx (Master file)
Locations.xlsx
Products.xlsx
I would like to add two excel files to a template (combined three files into one), and do so formatting.
The Template.xlsx file has a total of six tabs. I would like to import the Locations.xlsx and the Products.xlsx in the Template.xlsx file. I would like to import the worksheets in existing tabs (i.e. Locations.xlsx would go to the “ Location Information “ tab in the Template.xlsx and Products.xlsx would go to the “Production Information” tab. Once the file are brought into the template, I would like to do some formatting (define decimal places, center text, remove duplicate values, etc.)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Can this be done in one macro?

 

[Trevor G]

Welcome to the forum, you aren't supplying all the information that would be needed to help you, but here is some code that will open 3 workbooks and copy data and close the 2 workbooks. Change file path and sheet names.

If you could be more specific you will get your solution. Which sheets to format and what are you looking for in the way of duplicates, which ranges to check.

The code below is looking to copy all the cells in each sheet, but is that what you want to happen?

Sub fileImport()
Workbooks.Open "C:\FilePath\Template.xlsx"
Workbooks.Open "C:\FilePath\Product.xlsx"
Sheets("SheetName").Activate
Cells.Copy
ActiveWorkbook.Close
Sheets("Production Information").Activate
Cells.PasteSpecial xlPasteAll
Workbooks.Open "C:\FilePath\Location.xlsx"
Sheets("SheetName").Activate
Cells.Copy
ActiveWorkbook.Close
Sheets("Location Information").Activate
Cells.PasteSpecial xlPasteAll
ActiveWorkbook.Save
End Sub

 

[sparky667]

Trevor,

You rock! Thanks for the quick reply.. I will see if I can provide more detail alter, still in the middle of another project.

The duplicate are in the workbooks I am merging to the template. The Duplciate values are number fields. I will post example of the files later when I have more time.

 

[sparky667]

OK, I just realized that you can't post attachments. SO I will try to clarify

The final file will be a spreadsheet named Template.xls.
Template.xls has five tabs (Cover Letter, FAQ, Contacts, Location Information and Product Information) I would like to import data for the Location Information and Product Information.

On the Template.xls (containing five tabs) I would like the Location Information tab populated with data from the Locataion.xls file. Also, I would like the Product Information tab populated with data from the Products.xls file.

Once I have imported the data from the Location.xls and Proiducts.xls spreadsheets to teh Template spreadsheet, I love like to proceed with formatting (defintie decimal places, center text, removed duplicate values from the imported data.)

I hope this clarifies what I am looking for.

The code you entered looks like what I need. I am uncertain what name the imported tabs will assume? I guess I will need to test this.