Remove front Value ( EG : Status = ) and make it a Header Name

Vgabond

Vgabond

Board Regular
Joined
Jul 22, 2008
Messages
197
Gurus

I have a data from Column A to Column BB. The data can go up till 500,000 lines or more. I have record a macro for the changes that I've done. The macro works pretty well but it run really slow which I believes due to it has a lot of redundant code that can be simplify to make it faster.
On top of that, if any of you can also add a code where I don't need to change the file name :-
Code: 
 Workbooks.Open Filename:= _
        "D:\Work\Total_Contacted\2016\Dec2016.xlsx"


but instead it will pick up the file in the folder when I run the code.

Full Macro as below:-

Code: 
Sub Macro1()
'
' Macro1 Macro
'


'
    ChDir "D:\Work\Total_Contacted\2016"
    Workbooks.Open Filename:= _
        "D:\Work\Total_Contacted\2016\Dec2016.xlsx"
    ActiveWindow.ScrollRow = 201318
    ActiveWindow.ScrollRow = 200445
    ActiveWindow.ScrollRow = 199135
    ActiveWindow.ScrollRow = 197825
    ActiveWindow.ScrollRow = 196515
    ActiveWindow.ScrollRow = 195205
    ActiveWindow.ScrollRow = 193894
    ActiveWindow.ScrollRow = 192148
    ActiveWindow.ScrollRow = 190838
    ActiveWindow.ScrollRow = 189091
    ActiveWindow.ScrollRow = 187344
    ActiveWindow.ScrollRow = 185161
    ActiveWindow.ScrollRow = 182104
    ActiveWindow.ScrollRow = 178610
    ActiveWindow.ScrollRow = 175116
    ActiveWindow.ScrollRow = 170750
    ActiveWindow.ScrollRow = 168566
    ActiveWindow.ScrollRow = 165946
    ActiveWindow.ScrollRow = 163326
    ActiveWindow.ScrollRow = 158959
    ActiveWindow.ScrollRow = 155028
    ActiveWindow.ScrollRow = 150661
    ActiveWindow.ScrollRow = 147605
    ActiveWindow.ScrollRow = 144111
    ActiveWindow.ScrollRow = 140617
    ActiveWindow.ScrollRow = 137124
    ActiveWindow.ScrollRow = 135377
    ActiveWindow.ScrollRow = 133630
    ActiveWindow.ScrollRow = 133194
    ActiveWindow.ScrollRow = 132757
    ActiveWindow.ScrollRow = 131447
    ActiveWindow.ScrollRow = 130137
    ActiveWindow.ScrollRow = 127953
    ActiveWindow.ScrollRow = 125770
    ActiveWindow.ScrollRow = 123150
    ActiveWindow.ScrollRow = 121403
    ActiveWindow.ScrollRow = 117909
    ActiveWindow.ScrollRow = 114852
    ActiveWindow.ScrollRow = 112232
    ActiveWindow.ScrollRow = 110049
    ActiveWindow.ScrollRow = 107428
    ActiveWindow.ScrollRow = 105245
    ActiveWindow.ScrollRow = 102625
    ActiveWindow.ScrollRow = 100441
    ActiveWindow.ScrollRow = 97821
    ActiveWindow.ScrollRow = 95638
    ActiveWindow.ScrollRow = 93017
    ActiveWindow.ScrollRow = 90834
    ActiveWindow.ScrollRow = 89524
    ActiveWindow.ScrollRow = 87777
    ActiveWindow.ScrollRow = 86467
    ActiveWindow.ScrollRow = 85157
    ActiveWindow.ScrollRow = 84284
    ActiveWindow.ScrollRow = 82973
    ActiveWindow.ScrollRow = 82537
    ActiveWindow.ScrollRow = 82100
    ActiveWindow.ScrollRow = 81663
    ActiveWindow.ScrollRow = 81227
    ActiveWindow.ScrollRow = 80790
    ActiveWindow.ScrollRow = 79917
    ActiveWindow.ScrollRow = 79480
    ActiveWindow.ScrollRow = 78170
    ActiveWindow.ScrollRow = 77733
    ActiveWindow.ScrollRow = 76423
    ActiveWindow.ScrollRow = 75550
    ActiveWindow.ScrollRow = 74239
    ActiveWindow.ScrollRow = 72929
    ActiveWindow.ScrollRow = 71619
    ActiveWindow.ScrollRow = 69873
    ActiveWindow.ScrollRow = 68562
    ActiveWindow.ScrollRow = 67252
    ActiveWindow.ScrollRow = 66816
    ActiveWindow.ScrollRow = 65942
    ActiveWindow.ScrollRow = 64632
    ActiveWindow.ScrollRow = 64195
    ActiveWindow.ScrollRow = 62449
    ActiveWindow.ScrollRow = 60702
    ActiveWindow.ScrollRow = 59392
    ActiveWindow.ScrollRow = 58518
    ActiveWindow.ScrollRow = 57645
    ActiveWindow.ScrollRow = 57208
    ActiveWindow.ScrollRow = 56335
    ActiveWindow.ScrollRow = 55025
    ActiveWindow.ScrollRow = 54151
    ActiveWindow.ScrollRow = 53278
    ActiveWindow.ScrollRow = 52405
    ActiveWindow.ScrollRow = 51968
    ActiveWindow.ScrollRow = 51531
    ActiveWindow.ScrollRow = 51095
    ActiveWindow.ScrollRow = 50658
    ActiveWindow.ScrollRow = 50221
    ActiveWindow.ScrollRow = 49348
    ActiveWindow.ScrollRow = 48038
    ActiveWindow.ScrollRow = 46728
    ActiveWindow.ScrollRow = 45417
    ActiveWindow.ScrollRow = 43671
    ActiveWindow.ScrollRow = 41051
    ActiveWindow.ScrollRow = 39740
    ActiveWindow.ScrollRow = 38430
    ActiveWindow.ScrollRow = 37120
    ActiveWindow.ScrollRow = 35810
    ActiveWindow.ScrollRow = 34500
    ActiveWindow.ScrollRow = 32753
    ActiveWindow.ScrollRow = 31880
    ActiveWindow.ScrollRow = 31006
    ActiveWindow.ScrollRow = 30133
    ActiveWindow.ScrollRow = 29260
    ActiveWindow.ScrollRow = 28386
    ActiveWindow.ScrollRow = 27513
    ActiveWindow.ScrollRow = 26203
    ActiveWindow.ScrollRow = 24893
    ActiveWindow.ScrollRow = 23583
    ActiveWindow.ScrollRow = 22273
    ActiveWindow.ScrollRow = 21399
    ActiveWindow.ScrollRow = 20962
    ActiveWindow.ScrollRow = 20089
    ActiveWindow.ScrollRow = 19652
    ActiveWindow.ScrollRow = 19216
    ActiveWindow.ScrollRow = 18342
    ActiveWindow.ScrollRow = 17906
    ActiveWindow.ScrollRow = 17032
    ActiveWindow.ScrollRow = 16595
    ActiveWindow.ScrollRow = 16159
    ActiveWindow.ScrollRow = 14849
    ActiveWindow.ScrollRow = 14412
    ActiveWindow.ScrollRow = 13539
    ActiveWindow.ScrollRow = 13102
    ActiveWindow.ScrollRow = 12665
    ActiveWindow.ScrollRow = 12229
    ActiveWindow.ScrollRow = 11792
    ActiveWindow.ScrollRow = 11355
    ActiveWindow.ScrollRow = 10918
    ActiveWindow.ScrollRow = 10045
    ActiveWindow.ScrollRow = 9608
    ActiveWindow.ScrollRow = 8298
    ActiveWindow.ScrollRow = 7862
    ActiveWindow.ScrollRow = 6988
    ActiveWindow.ScrollRow = 5241
    ActiveWindow.ScrollRow = 3495
    ActiveWindow.ScrollRow = 3058
    ActiveWindow.ScrollRow = 2621
    ActiveWindow.ScrollRow = 1748
    ActiveWindow.ScrollRow = 874
    ActiveWindow.ScrollRow = 1
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "record_id"
    Columns("A:A").Select
    Selection.Replace What:="record_id", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "record_id"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "contact_info"
    Columns("B:B").Select
    Selection.Replace What:="contact_info=", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "record_type"
    Columns("C:C").Select
    Selection.Replace What:="record_type=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "record_status"
    Range("D1").Select
    Selection.Copy
    Columns("D:D").Select
    Selection.Replace What:="record_status=", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "call_result"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "call_result"
    Columns("E:E").Select
    Selection.Replace What:="call_result=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "attempt=1"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "attempt"
    Columns("F:F").Select
    Selection.Replace What:="attempt=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "dial_sched_time"
    Columns("G:G").Select
    Selection.Replace What:="dial_sched_time=", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "call_time"
    Columns("H:H").Select
    Selection.Replace What:="call_time=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "agent_id"
    Columns("I:I").Select
    Selection.Replace What:="agent_id=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "chain_n"
    Columns("J:J").Select
    Selection.Replace What:="chain_n=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("K:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "age"
    Columns("K:K").Select
    Selection.Replace What:="age=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "camp_id"
    Columns("L:L").Select
    Selection.Replace What:="camp_id=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "campaign_name"
    Columns("M:M").Select
    Selection.Replace What:="campaign_name=", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=4
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "customer_name"
    Columns("N:N").Select
    Selection.Replace What:="customer_name=", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.Goto Reference:="Header_Formatting!Macro1"
    Columns("O:O").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "FILENAME_DESC"
    Cells.Replace What:="FILENAME_DESC=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "gender"
    Columns("P:P").Select
    Selection.Replace What:="gender=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=1
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "PREFERED_CONTACT"
    Columns("Q:Q").Select
    Selection.Replace What:="PREFERED_CONTACT=", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "RACE"
    Columns("R:R").Select
    Selection.Replace What:="race=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Columns("S:S").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Save
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Save
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hey!

Try this below and see if it still does what you need to be done.... the file part of your question im not sure how to change that but i tried to clear up as much as i could

Code: 
Sub Macro1()
'
' Macro1 Macro
'




'
    ChDir "D:\Work\Total_Contacted\2016"
    Workbooks.Open Filename:= _
        "D:\Work\Total_Contacted\2016\Dec2016.xlsx"
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "record_id"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "contact_info"
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "record_type"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "record_status"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "call_result"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "attempt=1"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "attempt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "dial_sched_time"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "call_time"
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "agent_id"
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "chain_n"
    Columns("K:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "age"
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "camp_id"
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "campaign_name"
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "customer_name"
    Application.Goto Reference:="Header_Formatting!Macro1"
    Columns("O:O").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "FILENAME_DESC"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "gender"
    Columns("P:P").Select
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "PREFERED_CONTACT"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "RACE"
    Columns("S:AE").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Save
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Save
End Sub
 
Upvote 0
Nine Zero said:
Hey!

Try this below and see if it still does what you need to be done.... the file part of your question im not sure how to change that but i tried to clear up as much as i could

Code: 
Sub Macro1()
'
' Macro1 Macro
'




'
    ChDir "D:\Work\Total_Contacted\2016"
    Workbooks.Open Filename:= _
        "D:\Work\Total_Contacted\2016\Dec2016.xlsx"
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "record_id"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "contact_info"
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "record_type"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "record_status"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "call_result"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "attempt=1"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "attempt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "dial_sched_time"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "call_time"
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "agent_id"
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "chain_n"
    Columns("K:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "age"
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "camp_id"
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "campaign_name"
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "customer_name"
    Application.Goto Reference:="Header_Formatting!Macro1"
    Columns("O:O").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "FILENAME_DESC"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "gender"
    Columns("P:P").Select
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "PREFERED_CONTACT"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "RACE"
    Columns("S:AE").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Save
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Save
End Sub
Click to expand...

Hey there

Thank you so much. I have ran the code and it came out exactly as the previous recording marco and faster !! Awesome but it just that, it prompt error 1004. Do you have any ideas why?
 
Upvote 0
I just ran it and i also get the error at the red line in the code.... if you dont need it, go ahead and remove this line and you should have zero problems now

Code: 
Sub Macro1()
'
' Macro1 Macro
'




'
    ChDir "D:\Work\Total_Contacted\2016"
    Workbooks.Open Filename:= _
        "D:\Work\Total_Contacted\2016\Dec2016.xlsx"
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "record_id"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "contact_info"
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "record_type"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "record_status"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "call_result"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "attempt=1"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "attempt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "dial_sched_time"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "call_time"
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "agent_id"
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "chain_n"
    Columns("K:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "age"
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "camp_id"
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "campaign_name"
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "customer_name"
  [B][COLOR=#ff0000]  Application.Goto Reference:="Header_Formatting!Macro1"[/COLOR][/B]
    Columns("O:O").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "FILENAME_DESC"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "gender"
    Columns("P:P").Select
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "PREFERED_CONTACT"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "RACE"
    Columns("S:AE").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Save
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Save
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with Vba Code
Replies
3
Views
433
Coyotex3
C
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
297
Linda Lo
L
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
296
jolivanes
jolivanes
J
  • Solved
Assistance with excel macro
Replies
3
Views
244
dreid1011
dreid1011
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
173
Jiraya_00
J

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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