Code to access a hidden worksheet

[Guest]

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

 

[Guest]

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
....................................

 

[Guest]

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

 

[Dave Peterson]

You have another reply at your original post.

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