VBA Copy/ Pasting w/ Tricky File Name

[Littlemalky]

Hey can someone help me out with perfecting this line of code because I continue to get the error, "Subscript out of range", and I'm not sure what I'm doing wrong. I'm assuming that this code isn't exactly synonymous with the actual file name, but I don't know how to correct it.

One workbook that I'm copying the data from is called "detail w add.xlsx". The workbook that I'm pasting into is called, "Fullfillment 20110301 - Domestic.xlsx". The numbers will always be the current day's date formatted yyyymmdd. Can anyone help?

Sub DetailWAdd()
        Workbooks("detail w add.xlsx").Cells.Copy _
  [COLOR=red]      Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & "- Domestic" & ".xlsx").Worksheets("detail w add").Range("A1")
[/COLOR]    Application.CutCopyMode = False
    Workbooks("detail w add.xlsx").Close SaveChanges:=False 'Closes without saving changes
End Sub

 

[MrKowz]

Upon first glance, it looks like you forgot to include a space before the hyphen:

Sub DetailWAdd()
        Workbooks("detail w add.xlsx").Cells.Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") &[B][COLOR=red] " - Domestic"[/COLOR][/B] & ".xlsx").Worksheets("detail w add").Range("A1")
    Application.CutCopyMode = False
    Workbooks("detail w add.xlsx").Close SaveChanges:=False 'Closes without saving changes
End Sub

 

[Littlemalky]

I just tried to correct that as you suggested; however, when I run this macro the code is still highlighted yellow. Do I need to reflect the spaces differently?

 

[MrKowz]

Is your destination workbook open? If not, you need to open the workbook before VBA can paste to it.

 

[Littlemalky]

Yes, I have that line of code before everything else, I just didn't post it because I didn't want to disclose any kind of path or company information.

Sub DetailWAdd()
    Workbooks.Open filename:="[URL="file://\\Cobalt\Groups\Materials\DMS\Jesse's"]...[/URL]\Domestic\detail w add.xlsx"
    Workbooks("detail w add.xlsx").Cells.Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("A1")
    Application.CutCopyMode = False
    Workbooks("detail w add.xlsx").Close SaveChanges:=False 'Closes without saving changes
End Sub

 

[MrKowz]

"detail w add.xlsx" is open, but is "Fullfillment 20110301 - Domestic.xlsx" open?

 

[Littlemalky]

Oh I apologize, yes that one is open as well. I haven't written the code to open it yet as I'm just testing individual macros with the file open from me manually doing it. I still need to figure out how to open the most recent dated file, but I think that is somewhere on the forumns here.