Hi Everyone,

I was hoping someone could help me out with this script? I know it a bit rough but most of it works ok.(still working on it) I just need to change a bit of the script so it does not pull from the worksheet with the name 8-11-08 or aug 11. The information im looking to pull will always be in the last worksheets See the hi lighted area below in red is the problem.

One last thing if you take a look at my call script in the last part of this post is there an easier way to do what I'm doing. I know it works just seems like a lot of mess...



As Always Thanks for your help... Mike

Main Script
Sub map1()
'
' map1 Macro
'

'
    Dim A
    Dim B
    Dim C

    
    
    A = "G:\Production 2007\Hazleton Data 2008.xls"
    B = "G:\Production 2007\New Production Links\Varsity Production.xls"
    C = "G:\Production 2007\New Production Links\Large Area Production.xls"
 
    Workbooks.Open A
    Workbooks.Open B: Application.DisplayAlerts = False: Call unProtect
    
  
    Windows("Hazleton Data 2008.xls").Activate
 
    
    Call Find_F
    ActiveCell.FormulaR1C1 = "='[Varsity Production.xls]8-11-08'!R3C2"
    Call Find_G
    ActiveCell.FormulaR1C1 = "='[Varsity Production.xls]8-11-08'!R3C3"
    Call Find_H
    ActiveCell.FormulaR1C1 = "='[Varsity Production.xls]8-11-08'!R3C4"
    Call Find_I
    ActiveCell.FormulaR1C1 = "='[Varsity Production.xls]8-11-08'!R3C5"
    Call Find_F
      
    'ActiveWorkbook.Save
    B.Close
    
    Workbooks.Open C: Application.DisplayAlerts = False: Call unProtect
    
    Windows("Hazleton Data 2008.xls").Activate
    
    
    Call Find_J
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C3" 'C
    Call Find_K
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C4" 'D
    Call Find_M
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C6" 'F
    Call Find_N
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C7" 'G
    Call Find_O
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C8" 'H
    Call Find_P
    ActiveCell.FormulaR1C1 = "='[Large Area Production.xls]aug11'!R3C9" 'I
    Call Find_J
    Stop
    'ActiveWorkbook.Save
    Workbook.Close C
    
    
End Sub
Call Scripts
Sub Find_F()
Dim BCell, NBCell

Range("F4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "F" & CStr(i - 1)
NBCell = "F" & CStr(i - 2)
Exit Sub
Else
Range("F" & CStr(i + 1)).Select
End If
Next i
End Sub

Sub Find_G()
Dim BCell, NBCell

Range("G4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "G" & CStr(i - 1)
NBCell = "G" & CStr(i - 2)
Exit Sub
Else
Range("G" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_H()
Dim BCell, NBCell

Range("H4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "H" & CStr(i - 1)
NBCell = "H" & CStr(i - 2)
Exit Sub
Else
Range("H" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_I()
Dim BCell, NBCell

Range("I4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "I" & CStr(i - 1)
NBCell = "I" & CStr(i - 2)
Exit Sub
Else
Range("I" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_J()
Dim BCell, NBCell

Range("J4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "J" & CStr(i - 1)
NBCell = "J" & CStr(i - 2)
Exit Sub
Else
Range("J" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_K()
Dim BCell, NBCell

Range("K4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "K" & CStr(i - 1)
NBCell = "K" & CStr(i - 2)
Exit Sub
Else
Range("K" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_M()
Dim BCell, NBCell

Range("M4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "M" & CStr(i - 1)
NBCell = "M" & CStr(i - 2)
Exit Sub
Else
Range("M" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_N()
Dim BCell, NBCell

Range("N4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "N" & CStr(i - 1)
NBCell = "N" & CStr(i - 2)
Exit Sub
Else
Range("N" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_O()
Dim BCell, NBCell

Range("O4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "O" & CStr(i - 1)
NBCell = "O" & CStr(i - 2)
Exit Sub
Else
Range("O" & CStr(i + 1)).Select
End If
Next i
End Sub
Sub Find_P()
Dim BCell, NBCell

Range("P4").Select

For i = 4 To 28
If ActiveCell.Value = Empty Then
BCell = "P" & CStr(i - 1)
NBCell = "P" & CStr(i - 2)
Exit Sub
Else
Range("P" & CStr(i + 1)).Select
End If
Next i
End Sub