Hide Rows with VBA

[niravptl]

Hello,

So we have wrote a code that does so many things. But there is a last step we would like it to do. The step is that the data that is being pasted in nextRow=200 and down needs to be hidden. So basically after everything is pasted in the new sheets I would like cells 200 down to probably 600 to be hidden in the new sheets being created.

This is my current code I have

Sub CopyandPaste()
Application.ScreenUpdating = False
Sheets("Template").Visible = True
Application.ScreenUpdating = False
Sheets("MasterSheet").Visible = True
Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long
Dim sheetCount As Long
Dim selectedCells
Dim newSheet As Worksheet
Dim pt As PivotTable
On Error Resume Next
Application.ScreenUpdating = False
lastRow = Sheets("MasterSheet").Cells(Sheets("MasterSheet").Rows.Count, "A").End(xlUp).Row
selectedCells = Application.Selection.Value
Set pt = ActiveSheet.PivotTables("PivotTable1")
    pt.RefreshTable
        
For sheetCount = 1 To UBound(selectedCells, 1)
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Set newSheet = Sheets(Sheets.Count)
    newSheet.Name = selectedCells(sheetCount, 1)
    nextRow = 200
    For thisRow = 2 To lastRow
        If Sheets("MasterSheet").Cells(thisRow, "A").Value = selectedCells(sheetCount, 1) Then
            Sheets("MasterSheet").Cells(thisRow, "A").EntireRow.Copy Destination:=newSheet.Cells(nextRow, "A")
            nextRow = nextRow + 1
        End If
    Next thisRow
Next sheetCount
Sheets("MasterSheet").Activate
Range("A1").Select
Sheets("MasterSheet").Visible = False
Application.ScreenUpdating = True
Sheets("Template").Visible = False
Application.ScreenUpdating = True
End Sub

I would like to say thank you in advance!

 

[cerfani]

newSheet.Rows("200:600").Hidden = True

put that after the loop

if it might be something else than 600 then calculate it and save it to a variable...

newSheet.Rows("200:" & lastRow).Hidden = True

 

[niravptl]

Hey thanks for helping me but I don't know if I put it at the wrong place but it isn't working. @cerfani


This is what I have...

Sub CopyandPaste()
Application.ScreenUpdating = False
Sheets("Template").Visible = True
Application.ScreenUpdating = False
Sheets("MasterSheet").Visible = True
Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long
Dim alsoRow As Long
Dim sheetCount As Long
Dim selectedCells
Dim newSheet As Worksheet
Dim pt As PivotTable
On Error Resume Next
Application.ScreenUpdating = False
lastRow = Sheets("MasterSheet").Cells(Sheets("MasterSheet").Rows.Count, "A").End(xlUp).Row
selectedCells = Application.Selection.Value
Set pt = ActiveSheet.PivotTables("PivotTable1")
    pt.RefreshTable
        
For sheetCount = 1 To UBound(selectedCells, 1)
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Set newSheet = Sheets(Sheets.Count)
    newSheet.Name = selectedCells(sheetCount, 1)
    nextRow = 200
    For thisRow = 2 To lastRow
        If Sheets("MasterSheet").Cells(thisRow, "A").Value = selectedCells(sheetCount, 1) Then
            Sheets("MasterSheet").Cells(thisRow, "A").EntireRow.Copy Destination:=newSheet.Cells(nextRow, "A")
            nextRow = nextRow + 1
        End If
    Next thisRow
Next sheetCount
newSheet.Rows("200:600").Hidden = True
Sheets("MasterSheet").Activate
Range("A1").Select
Sheets("MasterSheet").Visible = False
Application.ScreenUpdating = True
Sheets("Template").Visible = False
Application.ScreenUpdating = True
End Sub

 

[SpillerBD]

Without determining the bottom row, you could assume row 600 as last row.
Insert before "Next sheetCount"

newSheet.Rows("200:600").EntireRow.Hidden = True

 

[niravptl]

Actually I got it to work thank you so much for your help. Thank You so much!!!!

 

[niravptl]

Thank You so much!!!

 

[cerfani]

I actually meant for you top place after the nested loop. The first loop is looping sheets and you need to hide rows for each sheet so it needed to be inside the sheet loop and after the row loop... but anyways you got it done regardless. Good job.