here is the working macro. It works fine when the worksheets are not hidden.
The problem happens at
Sub File Copy()
Sheets("lookup") and ("Staff_Report") are hidden

Can a code be inserted to unlock the workbook,display the worksheet, run the
macro then relock thr workbook?

Sub aSelectMonth()

' Set module level variable m_selecteddate to month desired

If ActiveCell.Value = "" Then
Exit Sub
End If

If m_selecteddate = "12:00:00 AM" Then
If IsDate(ActiveCell.Value) Then
Let m_selecteddate = ActiveCell
Range("D1").Select
Else: MsgBox "Please select month", vbOKOnly
Exit Sub
End If
Call CopyLOCCodes
End If

' If IsDate(ActiveCell.Value) Then
' Let m_selecteddate = ActiveCell
' Range("D1").Select
' Else: MsgBox "Please select month", vbOKOnly
' Exit Sub
' End If
'
Call CopyLOCCodes

End Sub

Sub CopyLOCCodes()
' Copy Lookup List to a Months Worksheets
' DIM Worksheet file names

Dim filename As String
Dim m_unit As String

Range("LOCCODES").Copy

Let m_unit = ActiveCell.Value

If m_unit = "" Then
Exit Sub
End If

Let filename = m_unit & " " & Format(m_selecteddate, "mmm yy") &
".xls"

ChDrive "I:\"
ChDir "I:\EDO\Staffing\Working Documents\SCHEDULES\" &
Format(m_selecteddate, "mmm")

' ChDrive "Q:\"
' ChDir "Q:\Staffing on 'Mhnoa2UsersGroupEdo' (J)\Working
Documents\SCHEDULES\" & Format(m_selecteddate, "mmm")
Workbooks.Open filename

Windows(filename).Activate

Call FileCopy

Call aSelectMonth

End Sub

Sub FileCopy()
Sheets("Lookup").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Staff_report").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A100").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A150").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("AM").Select

Range("G4").Select

ActiveWorkbook.Save
ActiveWorkbook.Close

Windows("2006 master schedule.xls").Activate
Application.CutCopyMode = False
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

' Call CopyLOCCodes
End Sub


"Dave Peterson" wrote:

I'm guessing that you're trying to select the hidden sheet (or select a range on
that hidden sheet) first.

You don't need to select a range to work with it:

dim myRng1 as range
dim destcell as range

with worksheets("sheet99")
set myrng1 = .range("a1:x99")
end with

set destcell = worksheets("hidden").range("b27")

myrng1.copy _
destination:=destcell



sir Lancelot wrote:

I have several Protected and shared workbooks on the network. All are
identical and have hidden worksheets. I have a master that does not have the
worksheets hidden which contains a macro that changes the information in a
name range. The macro works just fine, but when i run the macro it cant find
the sheet indicated in the other workbooks because the sheets are hidden. Is
there a solution with-out having to unprotect the other workbooks?

--

Dave Peterson