Why am I getting a runtime error when I tell VBA to ignore errors?

This is part of my subroutine that's causing me an error. Why is line 18, which says Sheets(currentSheet).Activate, causing me an runtime error 9 even though I put On Error Resume Next right before it?

Sub PayTVMigrator()

    Dim workbookSource As String
    Dim currentSheet As String
    Dim sourceRowcount As Integer
    Dim destinationRowcount As Integer
    Dim i As Integer
    
    workbookSource = ActiveWorkbook.Name
    Sheets(1).Activate
    Workbooks.Open ("H:\Macros\Pay-TV template.xlsm")
    Sheets(1).Activate
    
    For i = 1 To 20 
        currentSheet = ActiveSheet.Name
        Workbooks(workbookSource).Activate
        On Error Resume Next
        Sheets(currentSheet).Activate 
        On Error GoTo 0
        ActiveSheet.Protect Contents:=False
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
        Cells.UnMerge

Open in new window

shampouyaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
Check the accepted solution here. Although the question it self is irrelevant but it discussed setting the error handling which what you need
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28625108.html
0
shampouyaAuthor Commented:
Here is my entire code:

Option Explicit

Sub PayTVMigrator()

    Dim workbookSource As String
    Dim currentSheet As String
    Dim sourceRowcount As Integer
    Dim destinationRowcount As Integer
    Dim i As Integer

    On Error Resume Next
    
    workbookSource = ActiveWorkbook.Name
    Sheets(1).Activate
    Workbooks.Open ("H:\Macros\Pay-TV template.xlsm")
    Sheets(1).Activate
    
    For i = 1 To 30 ' grand tab-traversing loop begins here
        currentSheet = ActiveSheet.Name
        Workbooks(workbookSource).Activate
        On Error GoTo ErrorTab
        Sheets(currentSheet).Activate ' will just ignore a tab that does not exist in the source
        On Error Resume Next
        ActiveSheet.Protect Contents:=False
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
        Cells.UnMerge
        
        If ActiveSheet.Name = "Free TV (Low Budget)" Then
            End
        End If
        
        If ActiveSheet.Name = "PAY TV-S" Then
            Do
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
            Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                
            Range("A1").Select
            
            Do
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
            Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
        
            Cells.Find(What:="SUBSCRIBER RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(3, 0).Select 'so that the first group of column headers is not deleted
            Do
                If ActiveCell.Value = "SERVICE" Or ActiveCell.Value = "NAME" Or ActiveCell.Value = "" Then
                    ActiveCell.EntireRow.Delete
                Else
                    ActiveCell.Offset(1, 0).Select
                    sourceRowcount = sourceRowcount + 1
                End If
            Loop Until ActiveCell.Value = "" And ActiveCell.Offset(1, 0).Value = ""

            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="SUBSCRIBER RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
            Cells.EntireRow.Hidden = False
            ActiveCell.Offset(2, 0).Select
            If sourceRowcount > 1 Then
                ActiveCell.EntireRow.Copy
                Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
            End If
            
            Workbooks(workbookSource).Activate
            Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-sourceRowcount, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            ActiveCell.PasteSpecial xlPasteValues
            Workbooks(workbookSource).Activate
            Cells.Find(What:="INITIAL", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(1 + sourceRowcount, 2)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            ActiveCell.Offset(0, 1).Select
            Selection.PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues ' +1?
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues

            Workbooks(workbookSource).Activate
            Cells.Find(What:="CLIENT:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(2, 1)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Client:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="PRODUCT NO:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(1, 1)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Product No:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="GUILD:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(3, 2)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Guild:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="DAM:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="DAM:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues

            Workbooks(workbookSource).Activate
            Cells.Find(What:="APPLICABLE TO THIS PRODUCT:", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 1).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="applicable to this product", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=xlYes).Select
            ActiveCell.Offset(0, 5).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="JOB DESCRIPTION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Job Description", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="TAX ID", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Tax ID", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="EMPLOYEE NAME", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Employee Name", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="FSO SSN", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="FSO SSN", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues

            Workbooks(workbookSource).Activate
            Cells.Find(What:="FSO NAME", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="FSO Name", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="PLYR/CONT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="PLYR/Cont", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues

            Workbooks(workbookSource).Activate
            Cells.Find(What:="WORK STATE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Work State", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
            
            Workbooks(workbookSource).Activate
            Cells.Find(What:="PRORATA", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(5, 0)).Copy
            Workbooks("Pay-TV template.xlsm").Activate
            Cells.Find(What:="Pro Rata", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues

            i = 0 'hiding past rows begins here
            If ActiveSheet.Rows("17:17").Height = 0 Or ActiveSheet.Rows("18:18").Height = 0 Or ActiveSheet.Rows("19:19").Height = 0 Then
                Cells.EntireRow.Hidden = False
            Else
                Cells.Find(What:="Service Name", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0).Select
                Do
                    i = i + 1
                    If ActiveCell.Offset(i, 3).Value <> "" Or ActiveCell.Offset(i, 6).Value <> "" Then
                        ActiveCell.Offset(i, 0).EntireRow.Hidden = True
                    End If
                Loop Until ActiveCell.Offset(i + 1, 3).Value = "" And ActiveCell.Offset(i + 1, 6).Value = ""
            End If

        End If
        
        If ActiveSheet.Name = "VC" Then
        
            Do
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
            Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                
            Range("A1").Select
            
            Do
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
            Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
        
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then 'skip this tab if there is a 0 value for receipts in the cumulative row
                Range("A1").Select
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-sourceRowcount, 2)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Total Gross Receipts", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PERCENT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(1 + sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Percent Payable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                    
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            End If
        End If
        
        
        If ActiveSheet.Name = "DomBC" Then
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then 'skip this tab if there is a 0 value for receipts in the cumulative row
                
                Do
                    Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                    
                Range("A1").Select
                
                Do
                    Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                
                Range("A1").Select
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(-sourceRowcount, 2)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Receipts Subject to Residuals", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PERCENT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, -1), ActiveCell.Offset(1 + sourceRowcount, -1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Percent Payable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            End If
        End If


        If ActiveSheet.Name = "2% Foreign Pay TV" Then
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then 'skip this tab if there is a 0 value for receipts in the cumulative row
                Range("A1").Select
                Do
                    Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                Range("A1").Select
                
                Do
                    Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
            
                Range("A1").Select
                
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then ' this assumes that the good rows to keep are all marked by "CURRENT"
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(-sourceRowcount, 2)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Receipts Subject to Residuals", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PERCENT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, -1), ActiveCell.Offset(1 + sourceRowcount, -1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Percent Payable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                    
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            End If
        End If
        
        
        If ActiveSheet.Name = "EST - Perm. Downloads" Then
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then  'skip this tab if there is a 0 value for receipts in the cumulative row
            
                Range("A1").Select
                Do
                    Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
                Range("A1").Select
                
                Do
                    Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                    ActiveCell.Offset(2, 1).Value = ActiveCell.Offset(1, 1).Value
                Loop Until ActiveCell.Offset(2, 0).Value = "" And ActiveCell.Offset(3, 0).Value = ""
            
                Range("A1").Select
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then ' this assumes that the good rows to keep are all marked by "CURRENT"
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(-sourceRowcount, 3)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Gross Receipts at 20%", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            End If
        End If
        
        
        If ActiveSheet.Name = "In-Flight" Then
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then  'skip this tab if there is a 0 value for receipts in the cumulative row
                Range("A1").Select
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then ' this assumes that the good rows to keep are all marked by "CURRENT"
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-sourceRowcount, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Receipts Subject to Residuals", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PERCENT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(1 + sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Percent Payable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            End If
        End If
        
        If ActiveSheet.Name = "DTR" Then
            Cells.Find(What:="CUMULATIVE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
            If ActiveCell.Offset(0, 2).Value <> 0 Then  'skip this tab if there is a 0 value for receipts in the cumulative row
                Range("A1").Select
                Cells.Find(What:="REPORTING ", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(2, 0).Select
                Do
                    If ActiveCell.Value <> "CURRENT" Then ' this assumes that the good rows to keep are all marked by "CURRENT"
                        ActiveCell.EntireRow.Delete
                    Else
                        ActiveCell.Offset(1, 0).Select
                        sourceRowcount = sourceRowcount + 1
                    End If
                Loop Until ActiveCell.Value = "CUMULATIVE"
            
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="PERCENTAGE RESIDUALS", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="paytv2"
                Cells.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
                If sourceRowcount > 1 Then
                    ActiveCell.EntireRow.Copy
                    Range(ActiveCell, ActiveCell.Offset(sourceRowcount - 2, 0)).EntireRow.Insert (xlShiftDown)
                End If
            
                Workbooks(workbookSource).Activate
                
                Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-sourceRowcount, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Receipts Subject to Residuals", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PERCENT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, -1), ActiveCell.Offset(1 + sourceRowcount, -1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Percent Payable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="PENSIONABLE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Amount Pensionable", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:=" PENSION", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Pension Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
                
                Workbooks(workbookSource).Activate
                Cells.Find(What:="  TAXES", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(sourceRowcount + 1, 1)).Copy
                Workbooks("Pay-TV template.xlsm").Activate
                Cells.Find(What:="Tax Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=xlYes).Select
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(sourceRowcount, 0)).PasteSpecial xlPasteValues
            
            End If
        End If
        
        
NextTab:
    sourceRowcount = 0
    Workbooks("Pay-TV template.xlsm").Activate
    ActiveSheet.Next.Select
    Next i
    
    On Error GoTo 0
    
ErrorTab:
    Resume NextTab
    
    
End Sub

Open in new window

0
FarWestCommented:
maybe I did not get the full code idea, but I think the problem is in currentSheet which is not changing
if you want iterate through all workbook sheet then replace 30 with  worksheets.count
please take a look to code here which shouldn't generate error 9 at all
        Dim wbSource As Workbook
        wbSource = ActiveWorkbook
        workbookSource = ActiveWorkbook.Name
    
    Sheets(1).Activate
       Dim wbTemplate As Workbook
       
    Workbooks.Open ("H:\Macros\Pay-TV template.xlsm")
    Sheets(1).Activate
    wbTemplate = ActiveWorkbook
    
    
    For i = 1 To wbSource.Worksheets.Count ' grand tab-traversing loop begins here
        currentSheet = wbSource.Sheets(i).Name

Open in new window

also note it is better to have workbooks as objects and assign them as in the code and use them, also worksheet
you can use for example  For Each ws in wbSource.WorKsheets
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

aikimarkCommented:
Try this:
Sheets(i).Activate

Open in new window

0
shampouyaAuthor Commented:
This code isn't working for me, I can't initialize the wbSouce variable as the active workbook. The wbSource variable is just defined as nothing for the whole duration of the program

    Dim wbSource As Workbook
    wbSource = ActiveWorkbook

Open in new window

0
Martin LissOlder than dirtCommented:
wbSource is an Object, not a variable so you need to do

Set wbSource = ActiveWorkbook

or just replace "wbSource" with "ActiveWorkbook" anyplace it's used.
0
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.