This code has the path declared as a constant, strPath, at the top, you can use that throughout the rest of the code and you'll only need to change it in one place.

I've also added a few variables to reference the various worksheets.

This is only for the first part of the code, not really looked at the rest yet.

Actually the rest of the code seems quite different, it's manipulating the files being opened and doesn't appear to be copying anything from them to other worksheets/workbooks.

Sub WeeklyIntegrationSetupCIC()
' declare constant for folder path, change as required
Const strPath = "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\"

Dim wbDaily As Workbook
Dim wbTemplate As Workbook

    Application.DisplayAlerts = False
    Set wbTemplate = Workbooks.Open(Filename:= _
                                    "I:\ACCOUNTING\Clear Payments\Current Month\zIntegration template BLANK.xls")

    Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_CIC_DailyFile.xls")

    With wbDaily.ActiveSheet
        .Range("B7", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
                wbTemplate.Sheets("FeeSetup").Range("B7")
    End With

    wbDaily.Close SaveChanges:=False

    Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_GA_DailyFile.xls")

    With wbDaily.ActiveSheet
        .Range("B7", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
                wbTemplate.Sheets("FeeSetup").Range("B" & Rows.Count).End(xlUp).Offset(1)
    End With

    wbDaily.Close SaveChanges:=False