Code to access a hidden worksheet

Here is part of the working macro. It works fine when the worksheets are not
hidden.
When it runs and opens the workbook with hidden worksheets. 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, hide the worksheet, then relock the workbook?


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

try it,

Sheets("lookup").Visible = True
Sheets("Staff_Report").Visible = True
Sheets("Lookup").Select
..............................
...............................
Range("G4").Select
Sheets("lookup").Visible = False
Sheets("Staff_Report").Visible = False
....................................

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"sir Lancelot" wrote:

> Here is part of the working macro. It works fine when the worksheets are not
> hidden.
> When it runs and opens the workbook with hidden worksheets. 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, hide the worksheet, then relock the workbook?
>
>
> 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

I think you can greatly simplify your code by eliminating all that selecting.
Your problem likely comes from the fact that you can't activate hidden
worksheet or cells contained in hidden worksheets. You need to do this if you
are copying and pasting from the clipboard. However, you don't need to do it
this way. Just simply identify the cells and tell Excel to set these cells to
the desired value. This should work whether or not the sheets are hidden.
Example:

Sub FileCopy()
Dim x As Single
x = Sheet("Data").Range("J10")
Sheets("Lookup").Range("C2").Value = x
Sheets("Staff_report").Range("A1").Value = x
Range("A50").Value = x
Range("A100").Value = x
Range("A150").Value = x
etc...

End Sub

Regards,
Greg


"sir Lancelot" wrote:

> Here is part of the working macro. It works fine when the worksheets are not
> hidden.
> When it runs and opens the workbook with hidden worksheets. 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, hide the worksheet, then relock the workbook?
>
>
> 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

You have another reply at your original post.

sir Lancelot wrote:
>
> Here is part of the working macro. It works fine when the worksheets are not
> hidden.
> When it runs and opens the workbook with hidden worksheets. 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, hide the worksheet, then relock the workbook?
>
> 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