I am trying to put some code together where I can copy a list of dates from
one file to another if they are different with the destination file name
having part of the file name varying from month to month.
This is what I have got so far but I get the message subscript out of range.

Sub Update_Ethane_Email_File()
If MthNum < 10 Then
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\0" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
Else
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
End If
Workbooks.Open Filename:="M:\KOLOPSMB\Weekly and Monthly Emails\Ethane
Transfer Data " & MthName & ".xls", _
UpdateLinks:=0
Range("A5").Select
If ActiveCell.Value < DayOne Then
Windows("Create Email Notifications.xls").Activate
Range("Date_Field").Select
Selection.Copy
Windows("Ethane Transfer Data " & MthName & ".xls").Activate

End If
End Sub

Appreciate any help.

Thanks
Ray

Some tweaks. This may help you figure out your problems.

Option Explicit

Sub Update_Ethane_Email_File()
Dim oWB As Excel.Workbook
Dim EthaneWB As Excel.Workbook
Dim aWB As Excel.Workbook
Dim aWS As Excel.Worksheet

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

If MthNum < 10 Then
On Error Resume Next
Set oWB = Workbooks.Open(Filename:= _
"M:\KOLOPSMB\Post Conversion Files\CurrentYear\0" & MthNum & " " &
Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0)
On Error GoTo 0

Else
On Error Resume Next
Set oWB = Workbooks.Open(Filename:= _
"M:\KOLOPSMB\Post Conversion Files\CurrentYear\" & MthNum & " " &
Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0)
On Error GoTo 0

End If
If oWB Is Nothing Then
MsgBox ("Workbook not opened")
End If

On Error Resume Next
Set EthaneWB = Workbooks.Open(Filename:= _
"M:\KOLOPSMB\Weekly and Monthly Emails\EthaneTransfer Data " & MthName &
".xls", _
UpdateLinks:=0)
'Range("A5").Select
On Error GoTo 0

If Not EthaneWB Is Nothing Then
If EthaneWB.ActiveSheet.Range("A5").Value < DayOne Then
'If this is the active worksheet, do this

aWS.Range("Date_Field").Copy
'Windows("Create Email Notifications.xls").Activate
'Range("Date_Field").Select

'This may not be needed
EthaneWB.Activate
End If
End If
End Sub




"Ray Clark" wrote:

I am trying to put some code together where I can copy a list of dates from
one file to another if they are different with the destination file name
having part of the file name varying from month to month.
This is what I have got so far but I get the message subscript out of range.

Sub Update_Ethane_Email_File()
If MthNum < 10 Then
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\0" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
Else
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
End If
Workbooks.Open Filename:="M:\KOLOPSMB\Weekly and Monthly Emails\Ethane
Transfer Data " & MthName & ".xls", _
UpdateLinks:=0
Range("A5").Select
If ActiveCell.Value < DayOne Then
Windows("Create Email Notifications.xls").Activate
Range("Date_Field").Select
Selection.Copy
Windows("Ethane Transfer Data " & MthName & ".xls").Activate

End If
End Sub

Appreciate any help.

Thanks
Ray

Barb,

Thank you, your information gave me enough code to solve my problem.

Thanks heaps.
Ray

"Ray Clark" wrote:

I am trying to put some code together where I can copy a list of dates from
one file to another if they are different with the destination file name
having part of the file name varying from month to month.
This is what I have got so far but I get the message subscript out of range.

Sub Update_Ethane_Email_File()
If MthNum < 10 Then
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\0" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
Else
Workbooks.Open Filename:="M:\KOLOPSMB\Post Conversion Files\Current
Year\" & MthNum & " " & Left(MthName, 3) & " Data.xls", _
UpdateLinks:=0
End If
Workbooks.Open Filename:="M:\KOLOPSMB\Weekly and Monthly Emails\Ethane
Transfer Data " & MthName & ".xls", _
UpdateLinks:=0
Range("A5").Select
If ActiveCell.Value < DayOne Then
Windows("Create Email Notifications.xls").Activate
Range("Date_Field").Select
Selection.Copy
Windows("Ethane Transfer Data " & MthName & ".xls").Activate

End If
End Sub

Appreciate any help.

Thanks
Ray