Macro to Copy and Paste Values to File Based on Tab Name

[ksreed]

Hello,

I need to copy and paste a set of values from one file to another file based on tab name.

For instance, if I have a tab named "Sales," I would need to copy data in B1:B10 into the file in X:\USA\Regional\Sales.xls in D20:29.

Is there a way to reference the tab name to open a file and copy and paste the values into that file (I need to do this for about 20 or so tabs)?

Note: The tabs and files' names match one for one so I just need to add a directory reference somewhere in the macro.

Thanks!

 

[ksreed]

I have this code that needs to be repeated for 20 or so tabs.

1. What Loop or For statement do I need to use to move through all of the tabs in the "State Budget East.xls" file?

2. Is there a shortcut to cut down on the amount of code?

I would use this macro from the "State Budget East" file being open.

Thank you for any help.

    Workbooks.Open Filename:= _
    "X:\East\North Carolina\2011 North Carolina State budget.xls"
    Windows("State Budget East.xls").Activate
    Sheets("North Carolina").Select
    Range("G5:G16").Select
    Selection.Copy
    Windows("2011 North Carolina State budget.xls").Activate
    Sheets("Expenses").Select
    ActiveWindow.SmallScroll Down:=162
    Range("H196").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
 
    Workbooks.Open Filename:= _
    "X:\East\Virginia\2011 Virginia State budget.xls"
    Windows("State Budget East.xls").Activate
    Sheets("Virginia").Select
    Range("G5:G16").Select
    Selection.Copy
    Windows("2011 Virginia State budget.xls").Activate
    Sheets("Expenses").Select
    ActiveWindow.SmallScroll Down:=162
    Range("H196").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWorkbook.Save
    ActiveWindow.Close