Copying a worksheet from one workbook to another - Syntax issue

[Vonsteiner]

Hey there,
I have seen other posts about copying a worksheet from one workbook to another, but I can't seem to get the syntax correct. Let me explain what I am doing first. My workbook ("Master Invoice List") has two sheets to begin with: Sheet 1 ("Summary") and Sheet 2 ("Master"). "Master" has data from A:CR with a list of companies I need to invoice in E -- each company may have 1-100+ invoices. Once I have all the data in the Mater worksheet input, I create a new worksheet for each company with all their rows and data. Now the "Master Invoice List" has the original 2 sheets plus a sheet for each company ("Company Name") in the list from E of "Master". I have a macro already written to input the necessary formulas into each "Company Name". I then export all the "Company Name" worksheets into a file on a network drive. This is where I am running into my issue. I need some code to copy "Summary" into each "Company Name" files at the beginning of the "Company Name.xlsx" workbook. I can't seem to get the syntax correct to pull "Summary" into the new workbook. The name of the "Company Name.xlsx" file will not always be the same. Any help on this would be greatly appreciated. Thank you. This is the code I tried last:

Windows("Master Invoice List.xlsm").Activate
    sheets("Summary").Select
    sheets("Summary").Copy Before:=Workbooks("Company Name.xlsx").sheets(1)

This works but with the changing "Company Name.xlsx" it wouldn't really work. I have tried other various versions of the above, but since I deleted them to use this last one I don't really remember what they were.

 

[GWteB]

Does your macro open the destination company workbook before copying the summary sheet? If not, you should try.

 

[Vonsteiner]

I have tried it with both workbooks open; just the "Master Invoice List"; and just the "Company Name" workbook open. I am thinking it is a simple mistake (I hope so anyway) but it is one that is eluding me right now.

 

[Vonsteiner]

The code I input works within the specific "Company Name" workbook when I have both the "Master Invoice List" and "Company Name" workbooks open.

 

[GWteB]

Due to the file extensions in your code, the code has to be in the "Master Invoice List.xlsm". Just a remark: it's not necessary to activate certain windows or to select certain worksheets (unless you are using the ActiveWorksheet object), for example:

Public Sub Example()
   
    ' ThisWorkbook < refers to the wbk "Master Invoice List.xlsm" since this code is in it

    ThisWorkbook.Sheets("Summery").Copy Before:=Workbooks("Company Name.xlsx").Sheets(1)

End Sub

 

[Vonsteiner]

Ok, that is the basically what I have before. I am looking for code that will copy the 'Summary' worksheet over to the "Company Name" workbook no matter what the company name is. If I have to have both workbooks open at the same time I can just as easily move the 'Summary' worksheet from one workbook to the other.