How do I paste information to hidden worksheets?

[Guest]

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]

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

 

[Guest]

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]

First, naming your sub FileCopy is probably a bad idea.

There's a FileCopy statement in VBA that could cause confusion--maybe not with
excel--but it sure would confuse me. I'd rename it.

I didn't test this, but I'd try this sort of code:

Sub myFileCopy()
Worksheets("Lookup").Range("C2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

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

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

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

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

'rest of your original code here.

End Sub

And yes, you can unprotect a workbook, display the worksheet, run the macro,
hide the sheet, reprotect the workbook.

Option Explicit
Sub testme02()
Dim myVisibility As Long
Dim myPWD As String

myPWD = "asdf"

With Workbooks("book1.xls")
.Unprotect Password:=myPWD
myVisibility = .Worksheets("Lookup").Visible
.Worksheets("lookup").Visible = xlSheetVisible
'do the work
MsgBox "do the work!"
.Worksheets("lookup").Visible = myVisibility
.Protect Password:=myPWD
End With

End Sub

(You could get most of this code by recording a macro when you do this
manually.)

But most of the time, there isn't a reason to do things this way. I think most
people find the:

worksheets("Lookup").select
range("a1").select
selection.pastespecial....

much more difficult to read/debug than:

worksheets("lookup").range("a1").pastespecial...

 

[Guest]

Thanks

First, naming your sub FileCopy is probably a bad idea.

There's a FileCopy statement in VBA that could cause confusion--maybe not with
excel--but it sure would confuse me. I'd rename it.

I didn't test this, but I'd try this sort of code:

Sub myFileCopy()
Worksheets("Lookup").Range("C2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

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

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

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

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

'rest of your original code here.

End Sub

And yes, you can unprotect a workbook, display the worksheet, run the macro,
hide the sheet, reprotect the workbook.

Option Explicit
Sub testme02()
Dim myVisibility As Long
Dim myPWD As String

myPWD = "asdf"

With Workbooks("book1.xls")
.Unprotect Password:=myPWD
myVisibility = .Worksheets("Lookup").Visible
.Worksheets("lookup").Visible = xlSheetVisible
'do the work
MsgBox "do the work!"
.Worksheets("lookup").Visible = myVisibility
.Protect Password:=myPWD
End With

End Sub

(You could get most of this code by recording a macro when you do this
manually.)

But most of the time, there isn't a reason to do things this way. I think most
people find the:

worksheets("Lookup").select
range("a1").select
selection.pastespecial....

much more difficult to read/debug than:

worksheets("lookup").range("a1").pastespecial...