ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro to run on hidden sheet (https://www.excelbanter.com/excel-discussion-misc-queries/235123-macro-run-hidden-sheet.html)

Tel
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

Don Guillett
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


Tel
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



Jim Thomlinson
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

Don Guillett
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


All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com