Macro to hide rows

[abanky]

So basically, I have this macro... the **.... ** is my problem area (and in the macro the * are not there).


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sh As Worksheet
Application.ScreenUpdating = False
If Target.Address = "$J$3" Then
For Each Sh In Worksheets()
If UCase(Sh.Name) = "CENSUS" Or UCase(Sh.Name) = "CALCS" Then

Sh.Unprotect ("password")

Sh.Rows("22:261").EntireRow.Hidden = False

Select Case Target.Value

Case 1 To 119

Sh.Rows(Target.Value * 2 + 22 & ":261").EntireRow.Hidden = True
**Range("A36:AN37").Select
Selection.AutoFill Destination:=Range("A36:AN41"), Type:=x1fillDefault
Range("A36:AN37").Select**

Case 120

'do nothing

Case Else

Target.Value = 120

End Select

Sh.Protect ("password"), DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

End If

Next Sh

End If
Application.ScreenUpdating = True
End Sub

So what happens now without the **...**, is that when i enter a number in J3 it hides or unhides all the rows in the particular section... if i enter 1, row 22 & row 23 are unhide, if I enter 2, rows 22 through 25 are unhidden... works perfectly.. but now i want to modify it.

I want to only keep the formulas on my master in rows 22 & row 23 and when i enter 2, it copies down the formulas from 22 and 23 into 24 and 25... formulas in 22 are not equal to the formulas in 23... i want to do this for 60 cases (as that is how i designed the macro to work)

 

[VoG]

Try

sh.Rows(Target.Value * 2 + 22 & ":261").EntireRow.Hidden = True
sh.Range("A36:AN37").AutoFill Destination:=sh.Range("A36:AN41"), Type:=xlFillDefault

 

[abanky]

That was fast... Thank you for that...

Now how do i get it to do the same thing in that macro that if i wanted less rows, it would delete the formulas... in essentially, i just want the formulas to be there only for the number of rows showing... where 1 = 2 rows is the default

 

[VoG]

Maybe

With sh.Rows(Target.Value * 2 + 22 & ":261")
    .EntireRow.Hidden = True
    .Value = .Value
End With
sh.Range("A36:AN37").AutoFill Destination:=sh.Range("A36:AN41"), Type:=xlFillDefault

 

[abanky]

So I finely got back to this... here is where I am now... the above macro works ok, but i need it do do two things... 1) when I enter 3 into the J3, it doesn't copy the formula to further than I wanted it too... i tried to fix it, by putting in my last column in place of AN41... but now it copies it all the way down. Also, 2) if i have 3 cases and i decide to only have 2 cases, i need it do delete the formulas for the rows were case 3 was