AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of shampouya
shampouya
 asked on

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

Microsoft ExcelVBA
Avatar of undefined
Last Comment
Martin Liss
8/22/2022 - Mon
FarWest
Check the accepted solution here. Although the question it self is irrelevant but it discussed setting the error handling which what you need
https://www.experts-exchange.com/questions/28625108/VBA-msgbox-not-displaying.html
shampouya
ASKER
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

ASKER CERTIFIED SOLUTION
FarWest
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
aikimark
Try this:
Sheets(i).Activate

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
shampouya
ASKER
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

SOLUTION
Martin Liss
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
âš¡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss
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
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent