eh avanzado un poco con el codigo se que no esta muy bien tngo algunos errores pero es lo mas cercano que se me ah ocurrido pienso que la idea esta bien pero los codigos no tanto este es el codigo con el que estoy trabajando :

Private Sub exportar_datos()

Dim ac As Long
Dim z As Integer
Dim objExcel As Excel.Application
Dim xLibro As Excel.Workbook
Dim Col As Integer, Fila As Integer
Dim c As Integer
Dim d As Integer
Dim b As Integer
Dim a As Integer
z = 0
For ac = 1 To MSFlexGrid1.Rows - 1
If MSFlexGrid1.TextMatrix(ac, 0) = "CE_01" And MSFlexGrid1.TextMatrix(ac, 10) = "4001" Then
z = z + 1
End If
Next ac
Set objExcel = New Excel.Application
Set xLibro = objExcel.Workbooks.Open(FileName:="C:\Proyecto Komatsu\CE_01.xls")
objExcel.Visible = False
With xLibro
If Option1 = False And Option2 = False Then
MsgBox "Debe elegir un turno"
End If
If Option1 = True Then
objExcel.Sheets("Dia").Select
For b = cells(1, 2) To cells(1, 1000)
If DTPicker1.Value = b Then
c = b
End If
Next b
For a = cells(2, 1) To cells(192, 1)
If a = "4001" Then
d = a
End If
Next a
z = cells(c, d)
End If
If Option2 = True Then
objExcel.Sheets("Noche").Select
For b = cells(1, 2) To cells(1, 1000)
If DTPicker1.Value = b Then
c = b
End If
Next b
For a = cells(2, 1) To cells(192, 1)
If a = "4001" Then
d = a
End If
Next a
z = cells(c, d)
End If
End With
end sub

espero su ayuda gracias