Copy Worksheet Loop

[MasterLu]

i've been working on this code to copy a worksheet from one workbook to another workbook. first i have to open each workbook and then copy the name of "whatever worksheet" to the other workbook. 3 workbooks are involved:
workbook with the code in it with the spreadsheet to loop through, workbook to copy from, workbook to paste into (times 80 different sheets and corresponding workbooks i'll need to paste into).

when i get the actual "copy worksheet" part of the script it gives me error "subscript out of range."

i hope i haven't done too poor of a job explaining... any advice is greatly appreciated!!!
thanks for your help!!!!!!




Sub CopyEEO()

Dim FirstRow As Integer, LastRow As Integer
Dim Sht As Worksheet 'worksheet with list of names of worksheets to copy in column A and filepath to paste into in column B
Dim AGENCY As String 'column A values in Worksheets("EEO") contains list of names of worksheets to copy
Dim FileName As String 'column B values in Worksheets("EEO") contains filepath of workbook to paste into
Dim EEOFile As String 'file with worksheets to copy from

Set Sht = Worksheets("EEO")
FirstRow = 2
LastRow = 83
For x = FirstRow To LastRow
EEOFile = "C:\Documents and Settings\Owner\My Documents\Yakima.XLS"
AGENCY = Sht.Range("A" & x).Value
FileName = Sht.Range("B" & x).Value
Application.ScreenUpdating = False
Workbooks.Open EEOFile
Workbooks.Open FileName

'error occurs here
Workbooks(EEOFile).Worksheets("& AGENCY &").Copy After:=Workbooks("& FileName &").Sheet1
Workbooks("& FileName &").Close SaveChanges:=True

Next x
End Sub

 

[SkipVought]

Hi,

loose the quotes

        'error occurs here
        Workbooks(EEOFile).Worksheets(AGENCY).Copy After:=Workbooks(FileName).Sheet1
        Workbooks(FileName).Close SaveChanges:=True

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MasterLu]

Hi Skip

Thanks for your response! I just tried it without the quotes and it still gives me the same error 'subscript out of range' as i'm stepping though just as it gets to that part... any other suggestions? i appreciate it!!!

lu

 

[SkipVought]

Subscript out of range is an INCORRRECT sheet name.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884