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.
The error is in this section of code
On this line:
Help????
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????