S
Steve_n_KC
I need sheet 1 N6:N55 to be populated with the text from sheet 2 Rows 4-53
under columns C thru N dependent upon sheet 1 B5 matching one of the values
in C1 thru N1 on sheet 2 (sheet 1 B5 value is a formula reference from those
same C-N1 cells on sheet 2 so there will always be an exact match if it makes
a difference)
Contents of the newly acquired N6:N55 values are then used to Hide/UnHide
Rows 6:55 on sheet 1. What I have now does what I want it to BUT I will have
to maintain 12 different macros and assign them to objects...I'm hoping to be
able to have a single macro that will work on the active sheet when run from
keyboard.
Current code...
ActiveSheet.Unprotect Password:="'"
Rows("6:57").Select
Selection.EntireRow.Hidden = False
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 56
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "n").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "n").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "n")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "n"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
Range("B5").Select
ActiveSheet.Protect Password:="'"
End Sub
under columns C thru N dependent upon sheet 1 B5 matching one of the values
in C1 thru N1 on sheet 2 (sheet 1 B5 value is a formula reference from those
same C-N1 cells on sheet 2 so there will always be an exact match if it makes
a difference)
Contents of the newly acquired N6:N55 values are then used to Hide/UnHide
Rows 6:55 on sheet 1. What I have now does what I want it to BUT I will have
to maintain 12 different macros and assign them to objects...I'm hoping to be
able to have a single macro that will work on the active sheet when run from
keyboard.
Current code...
ActiveSheet.Unprotect Password:="'"
Rows("6:57").Select
Selection.EntireRow.Hidden = False
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 56
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "n").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "n").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "n")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "n"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
Range("B5").Select
ActiveSheet.Protect Password:="'"
End Sub