Thread: Macro to run on hidden sheet
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Jim Thomlinson Jim Thomlinson is offline
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