Macro to run on hidden sheet

T

Tel

Hi again guys,

I've got a Macro (rather long but basically copies and pastes contents from
various sources into a single spreadsheet). However, If I hide the "formula"
sheet it stops the Macro from working. How can I get the Macro to work while
keeping the "formula" sheet hidden?

Below is the macro: (I've cut out the irrelevant bits).
Sub Remediation_Plan()
'
' Remediation_Plan Macro
' Macro recorded 26/06/2009 by Terry B Glover
'

'

Sheets("Formula").Select
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Sheets("Formula").Select
Range("B10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.ClearContents
Range("E22").Select
Sheets("Formula").Select
Selection.Copy
Sheets("Remediation Plan").Select
Range("E23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Sheets("Formula").Select
Range("B18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E32").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Formula").Select
Range("B23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Remediation Plan").Select
Range("E33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-33
End Sub
 
D

Don Guillett

If the destination sheet is protected and the cells to copy to are locked
then you must unprotect>do your thing>protect. Your code can and should be
modified to remove selections.
 
T

Tel

Hi Don,


The Destination Sheet isn't protected but and the cells to copy aren't
locked. However, I'm taking it that by "hiding" the sheet it gives it
similar properties to protecting it. Therefore, is it possible to unprotect
at the beginning of references to that sheet and reprotect at the end?

If so, can you help with where I would insert the coding please.

Thanks

Tel
 
J

Jim Thomlinson

You can not select on a hidden sheet. 2 Solutions. Unhide the sheet at the
start of the code and hide it at the end. Or better yet modify the code to
remove the selects...

Sub Remediation_Plan()
'
' Remediation_Plan Macro
' Macro recorded 26/06/2009 by Terry B Glover
'

'

Dim wksFormulas As Worksheet
Dim wksRemediation As Worksheet

Set wksFormulas = Sheets("Formula")
Set wksRemediation = Sheets("Remediation Plan")

With wksRemediation
.Range("E5").Value = wksFormulas.Range("B4").Value
.Range("E7").Value = wksFormulas.Range("B5").Value
.Range("E9").Value = wksFormulas.Range("B6").Value
.Range("E11").Value = wksFormulas.Range("B7").Value
.Range("E13").Value = wksFormulas.Range("B8").Value
.Range("E15").Value = wksFormulas.Range("B9").Value
.Range("E17").Value = wksFormulas.Range("B10").Value
.Range("E19").Value = wksFormulas.Range("B11").Value
.Range("E20").Value = wksFormulas.Range("B12").Value
.Range("E21").Value = wksFormulas.Range("B13").Value
.Range("E22").Value = wksFormulas.Range("B14").Value
.Range("E23").Value = wksFormulas.Range("E22").Value
.Range("E24").Value = wksFormulas.Range("B15").Value
.Range("E25").Value = wksFormulas.Range("B16").Value
.Range("E27").Value = wksFormulas.Range("B17").Value
.Range("E28").Value = wksFormulas.Range("B18").Value
.Range("E29").Value = wksFormulas.Range("B19").Value
.Range("E30").Value = wksFormulas.Range("B20").Value
.Range("E31").Value = wksFormulas.Range("B21").Value
.Range("E32").Value = wksFormulas.Range("B22").Value
.Range("E33").Value = wksFormulas.Range("B23").Value

End With

End Sub
 
D

Don Guillett

As I recall, you may indeed copy to a hidden sheet but you cannot SELECT it
because it is hidden. So, rewrite your code, as I suggested to do withOUT
selections. Or, unhide>do your thing>hide. AND, If you don't want users to
be able to unhide use xlveryhidden in your code so that it can ONLY be
opened using code.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
End Down a second time 1
How to macro to relative position 6
Copy & paste macro for multiple worksheets 8
Looping Macro 5
Macro to copy/paste from one workbook to another 8
Updating macros with copied worksheet information 3
conso macro 0

Top