Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro to run on hidden sheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Tel Tel is offline
external usenet poster
  
Posts: 39
Default Macro to run on hidden sheet
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default Macro to run on hidden sheet
If the destination sheet is protected and the cells to copy to are locked
then you must unprotectdo your thingprotect. Your code can and should be
modified to remove selections.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Tel Tel is offline
external usenet poster
  
Posts: 39
Default Macro to run on hidden sheet
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

"Don Guillett" wrote:

If the destination sheet is protected and the cells to copy to are locked
then you must unprotectdo your thingprotect. Your code can and should be
modified to remove selections.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Macro to run on hidden sheet
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
--
HTH...

Jim Thomlinson


"Tel" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default Macro to run on hidden sheet
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, unhidedo your thinghide. 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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
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

"Don Guillett" wrote:

If the destination sheet is protected and the cells to copy to are locked
then you must unprotectdo your thingprotect. Your code can and should
be
modified to remove selections.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a Macro to look at Hidden Sheet alice Excel Discussion (Misc queries) 7 April 20th 07 09:16 AM
Macro Error When Connecting to Hidden Sheet Wuddus Excel Discussion (Misc queries) 5 August 3rd 06 03:34 PM
I need my Hidden Rows to stay hidden when I print the sheet. Rosaliewoo Excel Discussion (Misc queries) 2 July 20th 06 07:51 PM
how 2 hide row if row in another sheet is hidden? xz Excel Discussion (Misc queries) 1 February 26th 06 06:19 PM
Can a macro format a hidden sheet? Robert Excel Discussion (Misc queries) 1 February 9th 05 06:13 PM


All times are GMT +1. The time now is 07:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement