jmthompson
Well-known Member
- Joined
- Mar 31, 2008
- Messages
- 966
Good afternoon,
I have written the following macro to do a number of tasks, but ultimately it is supposed to generate a simple tab-delimited txt file, which it does, but it also inserts quotes around some, but not all data in column "D". No idea why.
Any thoughts?
I have written the following macro to do a number of tasks, but ultimately it is supposed to generate a simple tab-delimited txt file, which it does, but it also inserts quotes around some, but not all data in column "D". No idea why.
Any thoughts?
Code:
Sub Populate_Breakout()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim lastRow As Long
Dim lastRowTemp As Long
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
ThisWorkbook.SaveAs ("G:\PCard Directory\Closes\" & MyYear & " Closes\" & MyMonth & " " & MyYear & "\" & MyMonth & " " & MyYear & " Statement Recon.xls")
Sheets("Template").Select
lastRow = (Cells(Rows.Count, 7).End(xlUp).Row)
Dim c As Range
Range("M2:M" & lastRow).Select
For Each c In Selection.SpecialCells(xlCellTypeVisible)
If c <> "" Then
c.Offset(, -12).FormulaR1C1 = "=VLOOKUP(LEFT(RC[12],5),'Account list'!C:C[1],2,FALSE)"
End If
Next c
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:A" & lastRow).Value = Range("A2:A" & lastRow).Value
ThisWorkbook.Save
lastRowTemp = Cells(Rows.Count, 7).End(xlUp).Row
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyFile = ThisWorkbook.Name
MyFormula = "=VLOOKUP(RC[-2],'[" & MyFile & "]Template'!R2C1:R" & lastRowTemp & "C7,7,FALSE)"
Workbooks.Open Filename:= _
"G:\PCard Directory\Closes\JPMC PCARD Pmnt Proc Template.xls"
MyBreak = ActiveWorkbook.Name
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("C2:C" & lastRow - 1).FormulaR1C1 = MyFormula
Range("C2:C" & lastRow - 1).Value = Range("C2:C" & lastRow - 1).Value
Range("C2:C" & lastRow - 1).Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Workbooks(MyBreak).SaveAs ("G:\PCard Directory\Closes\" & MyYear & " Closes\" & MyMonth & " " & MyYear & "\" & MyMonth & " " & MyYear & " JPMC PCARD Pmnt Proc Template.xls")
MyBreak = (MyMonth & " " & MyYear & " JPMC PCard Pmnt Proc Template.xls")
Workbooks(MyBreak).Close
Workbooks(MyFile).Save
Workbooks(MyFile).SaveAs ("G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".xls")
Sheets("Template").Select
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells.Select
Selection.RemoveSubtotal
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "B").Value) = "" Then
Cells(i, "B").EntireRow.Delete
End If
Next i
Columns("B:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:H").Select
Selection.Delete Shift:=xlToLeft
Columns("G:I").Select
Selection.Delete Shift:=xlToLeft
Columns("H:N").Select
Selection.Delete Shift:=xlToLeft
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & lastRow + 1).Value = lastRow
Range("D" & lastRow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Range("D" & lastRow + 1).Value = Range("D" & lastRow + 1).Value
Range("D2:D" & lastRow + 1).Select
Selection.Replace What:=""""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ChDir "G:\PCard Directory\Closes\TRecs files"
ActiveWorkbook.SaveAs Filename:= _
"G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".txt", FileFormat:= _
xlText, CreateBackup:=False
Kill ("G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".xls")
Kill ("G:\PCard Directory\Closes\statement.txt.")
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub