Speed up macros w/ for each and lots of hidden sheets

[gmbristow91]

Hi, I have a workbook that has a lot of hidden sheets. I have a macro that copies all the data off of a hidden sheet onto the main worksheet and uses a "for each" function to hide the rows and columns on the main worksheet based on which rows and columns were hidden on the sheet the data was being copied from. My issue is that the more sheets there are in the worksheet, the longer this task takes. It's taking up to 30 seconds now.

Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
Application.ScreenUpdating = False
Application.EnableEvents = False
Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value


For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column


For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row


Call ProtectSheets
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Caleeco]

Hello Gmbristow,

Try turning off calculation mode while the code is running. Updated code below

Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With


Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value




For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column




For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row




Call ProtectSheets
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With


End Sub

Let me know how you get on
Thanks
Caleeco

 

[gmbristow91]

Hello Gmbristow,

Try turning off calculation mode while the code is running. Updated code below

Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With


Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value




For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column




For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row




Call ProtectSheets
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With


End Sub

Let me know how you get on
Thanks
Caleeco

Thank you Caleeco! This works well but not perfect. But I'm at around 2 seconds now instead of 20-30 seconds. I'm only going to add more hidden sheets so we will see in the future. Thank you!