VBA Code error for visible cells code

[kenadams378]

I have an error with this line of my code;

.Worksheets("A214").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy .Worksheets("A200").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

I'm unsure where this has an error, i have specifically coded it to copy only visible cells..

Any ideas??

My full code is;

Option Explicit

Sub copy_rows()
Dim lrow As Long, i As Long, j As Long, startrange As Long, endrange As Long
Dim pcent As Variant
Dim sname As String
Dim Fname As Variant
Dim mysheet As Variant
Dim wb As Workbook
Dim last_line As Variant
Dim Mylen As Variant
Dim Mypos As Variant
Dim MyFile As Variant
Dim HPath As Variant
Dim IPath As Variant

    'Prompt user for most recent S29 filepath'
    
    ChDir "\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\Source Files\"
    Fname = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the most recent S29 file")
    
    If Fname = "False" Then
        MsgBox "You have not selected an S29 file."
        Exit Sub
    End If
    Worksheets("Start").Range("C3").Value = Fname
    Workbooks.Open Fname, UpdateLinks:=False, ReadOnly:=True
    
    Fname = ActiveWorkbook.Name
    
    'Using the most recent S29 file, remove any applied filters then add a filter
    'on field four (Hiport Fund) and copy the data over to the Original Data sheet within the Credit Risk Work Tool'
    
    ThisWorkbook.Worksheets("Original Data").Cells.ClearContents
    lrow = Workbooks(Fname).Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Workbooks(Fname).Worksheets("Sheet1").Range("A1:DK" & lrow).Copy ThisWorkbook.Worksheets("Original Data").Range("A1")
    Application.CutCopyMode = False
     
    ThisWorkbook.Worksheets("A214").Cells.ClearContents
    lrow = Workbooks(Fname).Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Sheet1").Rows(1).AutoFilter field:=4, Criteria1:="A214"
    Workbooks(Fname).Worksheets("Sheet1").Range("A:DK").Copy ThisWorkbook.Worksheets("A214").Range("A1")
    Application.CutCopyMode = False
    Workbooks(Fname).Close False
    
    'Copy the A214 data into 3 new tabs, name the tabs A200, A220 & A240 then replace any reference to A214
    'in these sheets to A200, A220 & A240'
    
    With ThisWorkbook
        .Worksheets("A214").Rows(1).AutoFilter field:=4, Criteria1:="A214"
        
        .Worksheets("A214").Rows(1).Copy .Worksheets("A200").Rows(1)
        .Worksheets("A214").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy .Worksheets("A200").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Worksheets("A200").Cells.Replace "A214", "A200", xlPart
        
        .Worksheets("A214").Rows(1).Copy .Worksheets("A220").Rows(1)
        .Worksheets("A214").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy .Worksheets("A220").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Worksheets("A220").Cells.Replace "A214", "A220", xlPart
        
        .Worksheets("A214").Rows(1).Copy .Worksheets("A240").Rows(1)
        .Worksheets("A214").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy .Worksheets("A240").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Worksheets("A240").Cells.Replace "A214", "A240", xlPart
    
        'Using % on the Start Sheet, perform a sum (value x %) to each column containing, Price, Holding & Cost'
        
        For i = 6 To 12 Step 3
            pcent = .Worksheets("Start").Range("B" & i).Value
            sname = .Worksheets("Start").Range("A" & i).Value
        
            With .Worksheets(sname)
                lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            
                For j = 2 To lrow
                    .Range("V" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("V" & j).Value * pcent
                    .Range("W" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("W" & j).Value * pcent
                    .Range("AC" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AC" & j).Value * pcent
                    .Range("AD" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AD" & j).Value * pcent
                    .Range("AE" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AE" & j).Value * pcent
                    .Range("AF" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AF" & j).Value * pcent
                    .Range("AG" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AG" & j).Value * pcent
                    .Range("AH" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AH" & j).Value * pcent
                    .Range("AI" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AI" & j).Value * pcent
                    .Range("AJ" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AJ" & j).Value * pcent
                    .Range("AK" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AK" & j).Value * pcent
                    .Range("AL" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AL" & j).Value * pcent
                    .Range("AM" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AM" & j).Value * pcent
                    .Range("AN" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AN" & j).Value * pcent
                    .Range("AO" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AO" & j).Value * pcent
                    .Range("AP" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AP" & j).Value * pcent
                    .Range("AQ" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AQ" & j).Value * pcent
                    .Range("AR" & j).Value = ThisWorkbook.Worksheets("Original Data").Range("AR" & j).Value * pcent
                    
                Next j
                
            End With
        
        Next i
    
    End With
    
    'Copy the data from each tab (Original Data, A200, A220 & A240) create a new tab called "Final Ouput" and paste
    'in the data'
    
    
    If Not Evaluate("ISREF(Final Output!A1)") Then
        ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(Worksheets.Count)).Name = "Final Output"
    Else
        ThisWorkbook.Worksheets("Final Output").Cells.ClearContents
    End If
    
    ThisWorkbook.Worksheets("A214").Rows(1).Copy ThisWorkbook.Worksheets("Final Output").Rows(1)
    
    For Each mysheet In Array("Original Data", "A200", "A214", "A220", "A240")
        lrow = ThisWorkbook.Worksheets(mysheet).Range("A" & Rows.Count).End(xlUp).Row
        ThisWorkbook.Worksheets(mysheet).Range("A2:DK" & lrow).Copy ThisWorkbook.Worksheets("Final Output").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next mysheet
    
    'Add user name & macro run details to start sheet'
    With Sheets("Start")
        .Range("J19") = Format(Now, "dd-mmm-yy @ hh:mm:ss")
        .Range("J20") = UCase(CreateObject("WScript.Network").UserName)
    End With

End If

End Sub