What's wrong with my macro? "Corrupting" my sheet?

J

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Good morning,
I created the code below for a template for users to paste data (from a notepad file) into a "Data Dump" Sheet then run one macro which changes a date dd/mm/yyyy to separate columns for month and year and then creates a pivot table. (It also changes some names to consolidate data)

The macro works well the first time the macro is run. But if you decide to begin again by deleting the data in the data dump and pivot tabs, when you attempt to paste data from a notepad, the data doesn't filter into the cells correctly.

Any ideas?

Here is my code
Code: 
Sub Create_PivotTables()
    Application.ScreenUpdating = False
    
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Columns("F:H").Select
    Selection.NumberFormat = "General"
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Period"
    Range("G1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Year"
    
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "WIRE PAYMENT" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "WIRE/ACH PAYMENT" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
    Columns("H:H").Select
    Selection.Style = "Currency"
    
    Columns("M:M").Select
    Selection.Replace What:="DHL*", Replacement:="DHL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="*GRAINGER*", Replacement:="GRAINGER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" ATT*", Replacement:="AT&T", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="AT&T*", Replacement:="AT&T", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Selection.Replace What:="UPS*", Replacement:="UPS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Selection.Replace What:="KROGER*", Replacement:="KROGER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Selection.Replace What:="LOWES*", Replacement:="LOWES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Selection.Replace What:="LOWE'S*", Replacement:="LOWES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  Selection.Replace What:="MEIJER*", Replacement:="MEIJER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  Selection.Replace What:="OFFICE DEPOT*", Replacement:="OFFICE DEPOT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  Selection.Replace What:="STAPLES*", Replacement:="STAPLES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="AMOCO*", Replacement:="AMOCO", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="BEST BUY*", Replacement:="BEST BUY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  Selection.Replace What:="BESTBUYCOM*", Replacement:="BEST BUY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="TARGET*", Replacement:="TARGET", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="SHELL*", Replacement:="SHELL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="WAL-MART*", Replacement:="WAL-MART", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="CORP EX*", Replacement:="CORP EX", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="CVS*", Replacement:="CVS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="FEDEX*", Replacement:="FEDEX", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="THE HOME DEPOT*", Replacement:="THE HOME DEPOT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="USPS*", Replacement:="USPS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="WALGREEN*", Replacement:="WALGREEN", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="EXXONMOBIL*", Replacement:="EXXONMOBIL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="DUNKIN*", Replacement:="DUNKIN", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="GFS MKTPLC*", Replacement:="GFS MKTPLC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Selection.Replace What:="HARBOR FREIGHT TOOLS*", Replacement:="HARBOR FREIGHT TOOLS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MACY*", Replacement:="MACY'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ARAMARK UNIFORM*", Replacement:="ARAMARK UNIFORM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ADVANCE AUTO PARTS*", Replacement:="ADVANCE AUTO PARTS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="DOLRTREE*", Replacement:="DOLLARTREE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="KMART*", Replacement:="KMART", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="XFR*", Replacement:="XFR", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="XFER*", Replacement:="XFR", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BIG LOTS*", Replacement:="BIG LOTS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ACME MARKETS*", Replacement:="ACME MARKETS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ACO-HARDWARE*", Replacement:="ACO-HARDWARE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ALLTEL*", Replacement:="ALLTEL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="APPLEBEE*", Replacement:="APPLEBEES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ARAMARK   *", Replacement:="ARAMARK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="AUTO PARTS UNLIMITED*", Replacement:="AUTO PARTS UNLIMITED", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="AUTOZONE*", Replacement:="AUTOZONE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="B AND B APPL*", Replacement:="B AND B APPL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BARNES & NOBLE*", Replacement:="BARNES & NOBLE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BATH & BODY WORKS*", Replacement:="BATH & BODY WORKS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="B & H PHOTO*", Replacement:="B & H PHOTO", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BATTERIES PLUS*", Replacement:="BATTERIES PLUS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BED BATH & BEYOND*", Replacement:="BED BATH & BEYOND", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BELK*", Replacement:="BELK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BLOCKBUSTER VIDEO*", Replacement:="BLOCKBUSTER VIDEO", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BORDERS BOOKS*", Replacement:="BORDERS BOOKS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BURGER KING*", Replacement:="BURGER KING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="BUSCH'S*", Replacement:="BUSCH'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="CIRCUIT CITY*", Replacement:="CIRCUIT CITY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="COMMUNITY COFFEE*", Replacement:="COMMUNITY COFFEE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="COMPUSA*", Replacement:="COMPUSA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="******* BARREL*", Replacement:="******* BARREL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="D&W*", Replacement:="D&W", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="DOLLAR GEN*", Replacement:="DOLLAR GENERAL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="DOMINOS*", Replacement:="DOMINOS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="DOMINO'S*", Replacement:="DOMINOS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="FELPAUSCH*", Replacement:="FELPAUSCH", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="FERGUSON ENT*", Replacement:="FERGUSON ENT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="FOOD WORLD*", Replacement:="FOOD WORLD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="GIANT FOOD*", Replacement:="GIANT FOOD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="GIANT-EAGLE*", Replacement:="GIANT-EAGLE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="gempler*", Replacement:="gempler", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="GODFATHERS PIZZA*", Replacement:="GODFATHERS PIZZA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="HAPPY HARRY'S*", Replacement:="HAPPY HARRY'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="HOBBY LOBBY*", Replacement:="HOBBY LOBBY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="JIMMY JOHN*", Replacement:="JIMMY JOHNS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="JOANN FABRIC*", Replacement:="JOANN FABRIC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="KOHL*", Replacement:="KOHLS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="LABSAFE*", Replacement:="LABSAFE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="LEXIS-NEXIS*", Replacement:="LEXIS-NEXIS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MARATHON OIL*", Replacement:="MARATHON OIL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MCDONALD'S*", Replacement:="MCDONALD'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MENARDS*", Replacement:="MENARDS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MICHAELS*", Replacement:="MICHAELS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="MOTION INDUSTRIES*", Replacement:="MOTION INDUSTRIES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="NEWARK INONE*", Replacement:="NEWARK INONE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="NORDSTROM*", Replacement:="NORDSTROM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="O'CHARLEY'S*", Replacement:="O'CHARLEY'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="OFFICE MAX*", Replacement:="OFFICE MAX", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="OUTBACK*", Replacement:="OUTBACK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="PANERA BREAD*", Replacement:="PANERA BREAD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="PAPA JOHNS*", Replacement:="PAPA JOHNS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="PIZZA HUT*", Replacement:="PIZZA HUT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="POPEYE*", Replacement:="POPEYES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="RADIO SHACK*", Replacement:="RADIO SHACK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="RADIOSHACK*", Replacement:="RADIOSHACK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ROTO-ROOTER*", Replacement:="ROTO-ROOTER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ROTO ROOTER*", Replacement:="ROTO-ROOTER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="RUBY TUESDAY*", Replacement:="RUBY TUESDAY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="RED LOBSTER*", Replacement:="RED LOBSTER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="RITE AID*", Replacement:="RITE AID", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SAFEWAY STORE*", Replacement:="SAFEWAY STORE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SAFEWAY GIFT*", Replacement:="SAFEWAY STORE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SEARS ROEBUCK*", Replacement:="SEARS ROEBUCK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SHERWIN WILLIAMS*", Replacement:="SHERWIN WILLIAMS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SPEEDWAY*", Replacement:="SPEEDWAY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="STARBUCKS*", Replacement:="STARBUCKS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="SUBWAY*", Replacement:="SAUBWAY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="OLIVE GARD*", Replacement:="OLIVE GARDEN", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="TRACTOR-SUPPLY*", Replacement:="TRACTOR SUPPLY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="TRADER JOE'S*", Replacement:="TRADER JOE'S", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="AOL*", Replacement:="AOL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="VERIZON WIRELESS*", Replacement:="VERIZON WIRELESS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="VERIZON WRLS*", Replacement:="VERIZON WIRELESS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="VRZWRLSS*", Replacement:="VERIZON WIRELESS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="WALMART*", Replacement:="WAL MART", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="WESCO*", Replacement:="WESCO", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="WINN-DIXIE*", Replacement:="WINN-DIXIE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="ZEE MEDICAL*", Replacement:="ZEE MEDICAL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
                                      
        
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "XFR" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
        
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastCell = Cells(lastRow, 24).Address
Set PRange = Range("A1:" & myLastCell)
Application.CommandBars("PivotTable").Visible = False
    Range("A1:" & myLastCell).Select
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        PRange).CreatePivotTable TableDestination:= _
        "'Budget Pivot'!R1C1", TableName:="Budget Pivot", DefaultVersion:= _
        xlPivotTableVersion10
    Sheets("Budget Pivot").PivotTables("Budget Pivot").AddFields RowFields:=Array( _
        "DEPARTMENT", "G/L ACCOUNT", "Employee Last Name"), ColumnFields:="Period", PageFields:=Array( _
        "Orig Merchant Name", "Year")
    Sheets("Budget Pivot").PivotTables("Budget Pivot").PivotFields("Transaction Amount"). _
        Orientation = xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = False
    
Sheets("Budget Pivot").Select
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
myLastCell = Cells(lastRow, 26).Address
Range("D6:" & myLastCell).Style = "Currency"
Range("A:A").ColumnWidth = 26.29
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Excel tries to be helpful here. Because you have run the Text To Columns function, when you paste the data in again, Excel tries to parse it using the same settings you used for the TTC operation. If you reset the TTC dialog by unchecking all the options before pasting, the problem should not occur.
 
Upvote 0
I knew the TTC was to blame!!!

Thank you, I added code to the end of my Macro to reset the TTC settings.
 
Upvote 0
Excel tries to be helpful here. Because you have run the Text To Columns function, when you paste the data in again, Excel tries to parse it using the same settings you used for the TTC operation
Click to expand...

I really hate that too.....:banghead:

It does that in normal usage too, not just in macros...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
318
Linda Lo
L
J
  • Solved
Assistance with excel macro
Replies
3
Views
249
dreid1011
dreid1011
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
299
jolivanes
jolivanes
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
307
dwalc
D
R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
375
rsulliva
R

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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