Batch Copy Certain Cells From Several Workbooks

**exarranum** · 04-19-2013, 02:36 AM

I've been trying to make a macro myself but I am getting nowhere slow.

I have made inventory templates (book and sheet) for our clients and their products, the templates are saved in excel start so that all new Workbooks created automatically have the same format. I have a made few updates to the templates and need to copy all entered data into a new workbook so that old and new documents are all the same format. The system is as follows;
Each workbook corresponds to a company and each sheet within corresponds to a product. New companies are added by simply creating a new workbook and naming it as the company’s name. New products are added by typing the name of the product into column A, a macro then automatically adds a new sheet of the same name.

The first sheet (Index) only needs data entered into column A, the rest of the columns will automatically calculate values based on the products sheet.

Every other sheet (Products) needs data entered in column A:G, G:N contain formulas.

So what I need to do is copy Column A from the index sheet to a new workbook of the same name, this will create new sheets for every product.
Then I need to copy A:G from every old sheet to the new sheets of the same name.

Thanks for any assistance!

Edit, here is my attempt so far for the product sheets, it works but is very slow.

Sub copydata()
'
' copydata Macro
'
    ActiveSheet.Range("a2:g2", ActiveSheet.Range("a1:g1").End(xlDown)).Select
    Selection.Copy
    Windows("Eastland.xlsm").Activate
    Range("a2:g2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'
End Sub

I need this to look in a folder and find a workbook that has the same name as it. Then find a sheet with the same name as the current sheet that is being copied from, once it's dont that it should move to the next sheet.