Why is my On Error GoTo not working?

J

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Good morning,
I am working on a large group of macros that will look for files in a specific folder, grab move data from those files into a master sheet, save a copy of the files to a new location and then delete the original file.

I am getting a Subscript out of range error when I run this macro. I know not all of the files exist at this time, but I thought my On Error GoTo would skip those. It works for the first file, but not the second and I don't know why.

Code: 
Sub IgnoreMe2()
Application.ScreenUpdating = False
'sets date standard
MyDate = Format(Now, "mm/dd/yy")
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
'sets CSV Path
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
'sets master WFO file path and name
MyMasterPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
'creates references to WFO master and CSV files- opens CSV file
Dim CurBook As Worksheet
 
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
Dim lastRowWFO As Integer
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
Dim wb As Workbook
    On Error GoTo BHAM
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "annarbor.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Dim SrceBook As Workbook
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13700 - The Ann Arbor News"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\annarbor" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "annarbor.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
BHAM:
     On Error GoTo ET
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "birmingham.csv"
        .Execute
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "12000 - The Birmingham News"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\birmingham" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "birmingham.csv"
SrceBook.Close
Application.DisplayAlerts = True
ET:
    On Error GoTo FJ
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "expresst.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "14600 - The Express-Times"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\expresst" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "expresst.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
FJ:
    On Error GoTo GRP
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "flintj.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13200 - The Flint Journal"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\flintj" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "flintj.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
GRP:
    On Error GoTo HUNT
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "grandr.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "12100 - The Grand Rapids Press"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\grandr" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "grandr.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
HUNT:
    On Error GoTo JCP
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "huntsville.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13500 - The Huntsville Times"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\huntsville" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "huntsville.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
JCP:
    On Error GoTo KG
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "jcpatriot.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "15000 - The Jackson Citizen Patriot"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\jcpatriot" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "jcpatriot.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
KG:
    On Error GoTo MPR
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "kalamazoo.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13600 - Kalamazoo Gazette"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\kalamazoo" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "kalamazoo.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
MPR:
    On Error GoTo MS
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "mobiler.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13000 - Press-Register (Mobile)"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\mobiler" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "mobiler.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
MS:
    On Error GoTo MC
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "mississippi.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "15200 - The Mississippi Press"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\mississippi" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "mississippi.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
MC:
    On Error GoTo NJN
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "muskegon.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "14800 - The Muskegon Chronicle"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\muskegon" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "muskegon.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
NJN:
    On Error GoTo PN
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "njersey.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "17100 - NJN Publishing"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\njersey" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "njersey.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
PN:
    On Error GoTo PD
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "patriot.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "12300 - The Patriot-News"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\patriot" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "patriot.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
PD:
    On Error GoTo PC
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "plaind.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "10200 - The Plain Dealer"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\plaind" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "plaind.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
PC:
    On Error GoTo PS
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*_postage.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "10200 - The Plain Dealer (PC)"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\PD postcards" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "*_postage.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
PS:
    On Error GoTo REP
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "pstandard.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "12200 - The Post-Standard"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\pstandard" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "pstandard.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
REP:
    On Error GoTo VALL
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "republican.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "13100 - The Republican"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\republican" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "republican.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
VALL:
    On Error GoTo SJ
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "saginaw_baycity.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "19130 - Valley Publishing"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\valley" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "saginaw_baycity.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
SJ:
    On Error GoTo SUN
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "sjersey.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "00000 - South Jersey Newspapers"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\sjersey" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "sjersey.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
SUN:
    On Error GoTo SAG
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "sunnews.csv"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "17200 - Sun News"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\sunnews" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "sunnews.csv"
SrceBook.Close
Application.DisplayAlerts = True
 
SAG:
    On Error GoTo BAY
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "saginaw.xls"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "14700 - The Saginaw News"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\saginaw" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "saginaw.xls"
SrceBook.Close
Application.DisplayAlerts = True
 
BAY:
    On Error GoTo ENDSUB
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "baycity.xls"
        .Execute
 
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
 
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Cells(Rows.Count, "A").End(xlUp).Row
CurBook.Activate
Cells(lastRowWFO + 1, "A").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
Cells(lastRowWFO + 1, "B").Value = "14900 - The Bay City Times"
Cells(lastRowWFO + 1, "C").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
Cells(lastRowWFO + 1, "D").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
Cells(lastRowWFO + 1, "E").Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
Cells(lastRowWFO + 1, "G").Formula = "=RC[-2]+RC[-1]"
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
SrceBook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\baycity" & MyDate & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "baycity.xls"
SrceBook.Close
Application.DisplayAlerts = True
 
Application.ScreenUpdating = True
ENDSUB:
End Sub


The error is in this section of code
Code: 
BHAM:
     On Error GoTo ET
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "birmingham.csv"
        .Execute
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With

On this line:
Code: 
Set wb = Workbooks.Open(.FoundFiles(1))


Help????
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
I think I get it, since what caused my second error isn't resolved, it doesn't have "room" for the second error.

Any ideas how I can work around this?
 
Upvote 0
MyDate = Format(Now, "mm/dd/yy")
That is not going to work as part of a path. I changed it to
MyDate = Format(Now, "mm.dd.yy")

As for your original problem. Whenever you find yourself duplicating anything in code, try and break it down into a reusable function. If you look at all of your blocks of code within this one procedure, there are really only three items that differ in each. You can take this code and place it into one procedure. The error handling logic will be simpler as well.

Take your data and place it into a worksheet named CSVList.
In this example, A1:C22

Code: 
annarbor.csv		13700 - The Ann Arbor News		annarbor
birmingham.csv		12000 - The Birmingham News		birmingham
expresst.csv		14600 - The Express-Times		expresst
flintj.csv		13200 - The Flint Journal		flintj
grandr.csv		12100 - The Grand Rapids Press		grandr
huntsville.csv		13500 - The Huntsville Times		huntsville
jcpatriot.csv		15000 - The Jackson Citizen Patriot	jcpatriot
kalamazoo.csv		13600 - Kalamazoo Gazette		kalamazoo
mobiler.csv		13000 - Press-Register (Mobile)		mobiler
mississippi.csv		15200 - The Mississippi Press		mississippi
muskegon.csv		14800 - The Muskegon Chronicle		muskegon
njersey.csv		17100 - NJN Publishing			njersey
patriot.csv		12300 - The Patriot-News		patriot
plaind.csv		10200 - The Plain Dealer		plaind
*_postage.csv		10200 - The Plain Dealer (PC)		PD postcards
pstandard.csv		12200 - The Post-Standard		pstandard
republican.csv		13100 - The Republican			republican
saginaw_baycity.csv	19130 - Valley Publishing		valley
sjersey.csv		00000 - South Jersey Newspapers		sjersey
sunnews.csv		17200 - Sun News			sunnews
saginaw.xls		14700 - The Saginaw News		saginaw
baycity.xls		14900 - The Bay City Times		baycity

Each row contains the three items that differ in each block.

See the code below to see how you can reuse your code in a loop and save yourself a lot of headaches. It may not work as I was not able to test it.

Code: 
Sub IgnoreMe2()
    Dim MyDate As String, MyMonth As String, MyYear As String, MyCSVPath As String
    Dim MyMasterPath As String, MyMaster As String, lastRowWFO As Long, CurBook As Worksheet
    Dim CSVListItem As Range
    
    Application.ScreenUpdating = False
    'sets date standard
    MyDate = Format(Now, "mm.dd.yy")
    MyMonth = Format(Now, "mmmm")
    MyYear = Format(Now, "yyyy")
    'sets CSV Path
    MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
    'sets master WFO file path and name
    MyMasterPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\"
    MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
     
    Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
    lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each CSVListItem In ThisWorkbook.Sheets("CSVList").Range("A1:A22")
        If Not OpenCsv(MyCSVPath, CStr(CSVListItem), CurBook, lastRowWFO, CSVListItem.Offset(, 1), _
            MyCSVPath & "Completed Postage Reports\" & CSVListItem.Offset(, 2) & MyDate & ".xls") Then
'            MsgBox "Failed..."
        End If
    Next


End Sub

Function OpenCsv(CSVPath As String, CSVFileName As String, CurBook As Worksheet, _
    lastRowWFO As Long, BValue As String, SaveAsPath As String) As Boolean
    
    Dim SrceBook As Workbook, lastRow As Long
    
    On Error GoTo Err_OpenCsv

    With Application.FileSearch
        .NewSearch
        .LookIn = CSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = CSVFileName
        .Execute
         Set SrceBook = Workbooks.Open(.FoundFiles(1))
    End With
 
    SrceBook.ActiveSheet.Name = "Sheet1"
    lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    With CurBook.Rows(lastRowWFO + 1)
        .Item(1).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
        .Item(2).Value = BValue
        .Item(3).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
        .Item(4).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
        .Item(5).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
        .Item(7).Formula = "=RC[-2]+RC[-1]"
    End With

    SrceBook.SaveAs SaveAsPath
    Application.DisplayAlerts = False
    Kill CSVPath & CSVFileName
    SrceBook.Close
    Application.DisplayAlerts = True
    OpenCsv = True
Err_OpenCsv:

End Function
 
Upvote 0
Sorry for the delay, I lust now got access to my computer with Excel.

The function works well in that it does open the available files and skips the unavailable files, but it doesnt grab the needed data and place it in the master list:

Code: 
Function OpenCsv(CSVPath As String, CSVFileName As String, CurBook As Worksheet, _
    lastRowWFO As Long, BValue As String, SaveAsPath As String) As Boolean
    
    Dim SrceBook As Workbook, lastRow As Long
    
    On Error GoTo Err_OpenCsv
    With Application.FileSearch
        .NewSearch
        .LookIn = CSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = CSVFileName
        .Execute
         Set SrceBook = Workbooks.Open(.FoundFiles(1))
    End With
 
    SrceBook.ActiveSheet.Name = "Sheet1"
    lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    With CurBook.Rows(lastRowWFO + 1)
        .Item(1).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
        .Item(2).Value = BValue
        .Item(3).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
        .Item(4).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
        .Item(5).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
        .Item(7).Formula = "=RC[-2]+RC[-1]"
    End With
    SrceBook.SaveAs SaveAsPath
    Application.DisplayAlerts = False
    Kill CSVPath & CSVFileName
    SrceBook.Close
    Application.DisplayAlerts = True
    OpenCsv = True
Err_OpenCsv:
End Function

For those existing files, the finctions skips from here:
Code: 
 .Item(1).Value = CSVPath & "[" & SrceBook.Name & "]Sheet1'A3"

to here:
Code: 
End Function

without even filling in the first cell on the master list.
 
Upvote 0
Okay, I had a problem with my lastrowWFO statement.

Now it is filling in information, but it is filling the all of the cells in a row with the same data...
 
Upvote 0
Okay, I've spent some more time on this and I see 3 problems-

1. Instead of copying the data from the source cell, the address to that cell is appearing in the master list- "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\2008\July\Daily CSV Files\[baycity.xls]Sheet1'A2" instead of "7/8/08"

2. Instead of filling the data in the same row, columns A, B, C, D, E & G, it is filling 6 rows, with each cell in that row filled with the same data

3. Instead of moving to the next available row, the function writes over existing data for each sourcefile. I think this is because of how and when lastRowWFO is defined.

Here is the code as it stands:

Code: 
Sub IgnoreMe2()
    Dim MyDate As String, MyMonth As String, MyYear As String, MyCSVPath As String
    Dim MyMasterPath As String, MyMaster As String, lastRowWFO As Long, CurBook As Worksheet
    Dim CSVListItem As Range
 
    Application.ScreenUpdating = False
    'sets date standard
    MyDate = Format(Now, "mm.dd.yy")
    MyMonth = Format(Now, "mmmm")
    MyYear = Format(Now, "yyyy")
    'sets CSV Path
    MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
    'sets master WFO file path and name
    MyMasterPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\"
    MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
 
    Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
    lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
 
    For Each CSVListItem In ThisWorkbook.Sheets("CSVList").Range("A1:A22")
        If Not OpenCsv(MyCSVPath, CStr(CSVListItem), CurBook, lastRowWFO, CSVListItem.Offset(, 1), _
            MyCSVPath & "Completed Postage Reports\" & CSVListItem.Offset(, 2) & MyDate & ".xls") Then
'            MsgBox "Failed..."
        End If
    Next
 
End Sub
Function OpenCsv(MyCSVPath As String, CSVFileName As String, CurBook As Worksheet, _
    lastRowWFO As Long, BValue As String, SaveAsPath As String) As Boolean
 
    Dim SrceBook As Workbook, lastRow As Long
 
    On Error GoTo Err_OpenCsv
    With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = CSVFileName
        .Execute
         Set SrceBook = Workbooks.Open(.FoundFiles(1))
    End With
 
    SrceBook.ActiveSheet.Name = "Sheet1"
    lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    With CurBook.Rows(lastRowWFO + 1)
        .Item(1).Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A3"
        .Item(2).Value = BValue
        .Item(3).Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'A2"
        .Item(4).Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'B" & lastRow
        .Item(5).Value = MyCSVPath & "[" & SrceBook.Name & "]Sheet1'E" & lastRow
        .Item(7).Formula = "=RC[-2]+RC[-1]"
    End With
    SrceBook.SaveAs SaveAsPath
    Application.DisplayAlerts = False
    Kill CSVPath & CSVFileName
    SrceBook.Close
    Application.DisplayAlerts = True
    OpenCsv = True
Err_OpenCsv:
End Function
 
Last edited:
Upvote 0
Does anybody have any ideas on how to solve the three previously mentioned problems I am having with this code???
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Run-time error '1004': Application definted or Object defined error
Replies
5
Views
470
MARK858
MARK858
A
  • Question
Filter by Date macro
Replies
6
Views
504
kevin9999
K
jalrs
  • Solved
Loop Working but gives me a run Time Error 1004
2
Replies
10
Views
1K
jalrs
jalrs
A
  • Question
VBA_Daily Average Vol
Replies
1
Views
388
offthelip
O
D
  • Question
Editing a macro to include taking the file and opening an email
Replies
0
Views
692
Domroy
D

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top