A
abbruno
I am trying to create a macro that is very similar to the one written b
Dave Peterson in this threa
http://www.excelbanter.com/showthread.php?t=207507
Dave's macro works great, but I needed to add a couple things to it an
have tried to create a loop, but I am getting an error.
Here is what I have. I am getting the following error "Run-time erro
'91. Object variable or With block variable not set" It seems this ha
something to do with the range I am trying to set, but I don't kno
where I am going wrong. I am fairly new to macros - Can anyone help?
Option Explicit
Sub Set_Q1_Targets_Results()
Sheets("Unhide").Visible = True
Sheets("last").Visible = True
Sheets("last1").Visible = True
Sheets("Home Equity First Lien").Select
Do While ActiveSheet.Name <> "Unhide"
Application.ScreenUpdating = False
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
With Worksheets(ActiveSheet)
Range("K14:K120").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Set myRng = .Range("Z14", .Cells(.Rows.Count, "Z").End(xlUp)) _
.SpecialCells(xlCellTypeConstants)
End With
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No Constants!"
Exit Sub
End If
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
.Formula = "=" & .Value
End With
Next myCell
Range("D14").Select
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Next.Select
Loop
ActiveWindow.SelectedSheets.Visible = False
End Su
Dave Peterson in this threa
http://www.excelbanter.com/showthread.php?t=207507
Dave's macro works great, but I needed to add a couple things to it an
have tried to create a loop, but I am getting an error.
Here is what I have. I am getting the following error "Run-time erro
'91. Object variable or With block variable not set" It seems this ha
something to do with the range I am trying to set, but I don't kno
where I am going wrong. I am fairly new to macros - Can anyone help?
Option Explicit
Sub Set_Q1_Targets_Results()
Sheets("Unhide").Visible = True
Sheets("last").Visible = True
Sheets("last1").Visible = True
Sheets("Home Equity First Lien").Select
Do While ActiveSheet.Name <> "Unhide"
Application.ScreenUpdating = False
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
With Worksheets(ActiveSheet)
Range("K14:K120").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Set myRng = .Range("Z14", .Cells(.Rows.Count, "Z").End(xlUp)) _
.SpecialCells(xlCellTypeConstants)
End With
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No Constants!"
Exit Sub
End If
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
.Formula = "=" & .Value
End With
Next myCell
Range("D14").Select
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Next.Select
Loop
ActiveWindow.SelectedSheets.Visible = False
End Su