coding for copying,pasting,eliminating "0" rows,formatting

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
I hope somebody here can help me with some coding issues. I have been trying to do it via Record Macro but I am stuck. I need to accomplish this report as soon as possible. I have tried to add the code using Mrexcel HTML maker but it didn't work. I also have a workbook but I don't know how to upload it. I will be glad to email it to anyone who can help.

Use code to accomplish a few things to multiples sheets in a single workbook – “AvsB–WANT”.
The first sheet named "ORIGINAL" is Actual vs. budget information (for multiple sites) updated from QB2016 to Excel 2007. Some of the sites have multiple units reporting.

Run code/macro to:
1. Copy 1st 4 columns (account name and numbers) from "ORIGINAL" to multiple sheets (14). – CODED.
a. The number of account numbers used can change from period to period.
2. Copy the last 8 columns of “ORIGINAL” to worksheet titled “TOTAL”. – CODED.
3. Copy other sites info to correct worksheet.
a. An issue here is whether I will need to break out individual reporting units within a site (see tab – “HW-want”) in order to delete non-zero accounts in a sub-site.
4. Size every other column starting with Column "F" - Column "MB" to .5. – CODED.
5. Size every other column starting with Column "E" - Column "MA" to fit contents.
6. In all worksheets except for "ORIGINAL" delete all rows with "0" or blank in all columns across sheet (column "E" through column "MA"). Otherwise ignore.
7. Save as” workbook. For example: “AvsB 022916.xlsm” or “AvsB 030716.xlsm”.
8. Select all yellow highlighted tabs to print.


The workbook contains the following:
a. ORIGINAL sheet. Need to breakout each individual site (and sub-site?) from this sheet to individual sheets. – Need code to look for title of site in “ORIGINAL” and copy the 7 columns related to that site to an individual tab. Not sure if I need to break out the site subs to individual tabs. I could use some advice here.
b. 14 sheets with yellow tab = 14 sites
c. 3 sheets with blue tab = 3 examples of how I would like the tabs to look like after cleanup – (AAA-want, CHNHW-want, and HP-want)
d. 4 sheets without highlighted tabs are no print sites (ORIGINAL-np, ACP16-np, 5PP16-np, and UNCL-np). Also, there are a few sites that have multiple subs which don’t need printed “CSB-oth, HP-oth, and HW-oth”.Need code to develop print areas. Here again I am not sure if I should put each individual sub on a separate tab.


Code: 
Sub SelectOriginalSheet()  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If UCase(ws.Name) Like "ORIGINAL*" Then
      ws.Activate
      Exit For
    End If
  Next ws
  Range("F:F,H:H,J:J,L:L,N:N,P:P,R:R").Select
    Range("R1").Activate
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    Range( _
        "F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP" _
        ).Select
    Range("AP1").Activate
    ActiveWindow.SmallScroll ToRight:=21
    Range( _
        "F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH" _
        ).Select
    Range("BH1").Activate
    ActiveWindow.SmallScroll ToRight:=18
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD" _
        ), Range("BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP")).Select
    Range("CB1").Activate
    ActiveWindow.SmallScroll ToRight:=20
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ" _
        ), Range( _
        "AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("CV1").Activate
    ActiveWindow.SmallScroll ToRight:=22
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,F:F,H:H,J:J,L:L,N:N" _
        ), Range( _
        "P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("DR1").Activate
    ActiveWindow.SmallScroll ToRight:=21
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH" _
        ), Range("BJ:BJ,BL:BL,BN:BN,BP:BP")).Select
    Range("EJ1").Activate
    ActiveWindow.SmallScroll ToRight:=20
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL" _
        ), Range( _
        "AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("FF1").Activate
    ActiveWindow.SmallScroll ToRight:=19
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,F:F,H:H,J:J,L:L,N:N,P:P,R:R" _
        ), Range( _
        "T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("FZ1").Activate
    ActiveWindow.SmallScroll ToRight:=22
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH" _
        ), Range("BJ:BJ,BL:BL,BN:BN,BP:BP")).Select
    Range("GV1").Activate
    ActiveWindow.SmallScroll ToRight:=16
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR" _
        ), Range( _
        "AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP")). _
        Select
    Range("HL1").Activate
    ActiveWindow.SmallScroll ToRight:=24
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T" _
        ), Range( _
        "V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("IJ1").Activate
    ActiveWindow.ScrollColumn = 219
    ActiveWindow.ScrollColumn = 221
    ActiveWindow.ScrollColumn = 223
    ActiveWindow.ScrollColumn = 224
    ActiveWindow.ScrollColumn = 226
    ActiveWindow.ScrollColumn = 228
    ActiveWindow.ScrollColumn = 229
    ActiveWindow.ScrollColumn = 230
    ActiveWindow.ScrollColumn = 231
    ActiveWindow.ScrollColumn = 232
    ActiveWindow.ScrollColumn = 233
    ActiveWindow.ScrollColumn = 235
    ActiveWindow.ScrollColumn = 236
    ActiveWindow.ScrollColumn = 237
    ActiveWindow.ScrollColumn = 238
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ" _
        ), Range("BL:BL,BN:BN,BP:BP")).Select
    Range("JF1").Activate
    ActiveWindow.SmallScroll ToRight:=18
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,JH:JH,JJ:JJ,JL:JL,JN:JN,JP:JP,JR:JR,JT:JT,JV:JV,JX:JX,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR" _
        ), Range( _
        "AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP")). _
        Select
    Range("JX1").Activate
    ActiveWindow.SmallScroll ToRight:=20
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,JH:JH,JJ:JJ,JL:JL,JN:JN,JP:JP,JR:JR,JT:JT,JV:JV,JX:JX,JZ:JZ,KB:KB,KD:KD,KF:KF,KH:KH,KJ:KJ,KL:KL,KN:KN,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB" _
        ), Range( _
        "AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("KN1").Activate
    ActiveWindow.SmallScroll ToRight:=16
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,JH:JH,JJ:JJ,JL:JL,JN:JN,JP:JP,JR:JR,JT:JT,JV:JV,JX:JX,JZ:JZ,KB:KB,KD:KD,KF:KF,KH:KH,KJ:KJ,KL:KL,KN:KN,KP:KP,KR:KR,KT:KT,KV:KV,KX:KX,KZ:KZ,LB:LB,LD:LD,LF:LF,LH:LH,F:F,H:H" _
        ), Range( _
        "J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP" _
        )).Select
    Range("LH1").Activate
    ActiveWindow.SmallScroll ToRight:=16
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,JH:JH,JJ:JJ,JL:JL,JN:JN,JP:JP,JR:JR,JT:JT,JV:JV,JX:JX,JZ:JZ,KB:KB,KD:KD,KF:KF,KH:KH,KJ:KJ,KL:KL,KN:KN,KP:KP,KR:KR,KT:KT,KV:KV,KX:KX,KZ:KZ,LB:LB,LD:LD,LF:LF,LH:LH,LJ:LJ,LL:LL" _
        ), Range( _
        "LN:LN,LP:LP,LR:LR,LT:LT,LV:LV,LX:LX,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD" _
        ), Range("BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP")).Select
    Range("LX1").Activate
    ActiveWindow.SmallScroll ToRight:=5
    Union(Range( _
        "BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR,DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB" _
        ), Range( _
        "ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FP:FP,FN:FN,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN" _
        ), Range( _
        "GP:GP,GR:GR,GT:GT,GV:GV,GX:GX,GZ:GZ,HB:HB,HD:HD,HF:HF,HH:HH,HJ:HJ,HL:HL,HN:HN,HP:HP,HR:HR,HT:HT,HV:HV,HX:HX,HZ:HZ,IB:IB,ID:ID,IF:IF,IH:IH,IJ:IJ,IR:IR,IT:IT,IL:IL,IN:IN,IP:IP,IV:IV,IX:IX,IZ:IZ" _
        ), Range( _
        "JB:JB,JD:JD,JF:JF,JH:JH,JJ:JJ,JL:JL,JN:JN,JP:JP,JR:JR,JT:JT,JV:JV,JX:JX,JZ:JZ,KB:KB,KD:KD,KF:KF,KH:KH,KJ:KJ,KL:KL,KN:KN,KP:KP,KR:KR,KT:KT,KV:KV,KX:KX,KZ:KZ,LB:LB,LD:LD,LF:LF,LH:LH,LJ:LJ,LL:LL" _
        ), Range( _
        "LN:LN,LP:LP,LR:LR,LT:LT,LV:LV,LX:LX,LZ:LZ,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB" _
        ), Range("BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP")).Select
    Range("LZ1").Activate
    Selection.ColumnWidth = 0.5
  ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("ORIGINAL-np").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("ACP16-np").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("AAA").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("CHNHW").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("CC516").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("CU1216").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("CSB").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("EP616").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("5PP16-np").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("HP").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("HW").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("MCHH").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("NC").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("UNCL-np").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("TOTAL").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("ORIGINAL-np").Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
    Range("LU90:MA90").Select
    Range("MA90").Activate
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("TOTAL").Select
    Range("E1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("ORIGINAL-np").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets(Array("ORIGINAL-np", "AAA", "CHNHW", "CC516", "CU1216", "CSB", "EP616", "HP", _
        "HW", "MCHH", "NC", "TOTAL", "ACP16-np", "5PP16-np", "UNCL-np")).Select
    Sheets("ORIGINAL-np").Activate
    Columns("A:C").Select
    Selection.ColumnWidth = 3
    Sheets("ORIGINAL-np").Activate
    Columns("D:MA").Select
    Columns("D:MA").EntireColumn.AutoFit
    ActiveWorkbook.Save
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

L
  • Question Question
error 1004 - delete column
Replies
7
Views
1K
LCC
L
D
  • Question Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
798
deZine
D
A
  • Solved
every time I run this code the auto filter only filters 1 column any ideals
Replies
1
Views
681
HaHoBe
HaHoBe
S
  • Solved
simplify the VBA code
Replies
8
Views
493
Peter_SSs
Peter_SSs
D
  • Question Question
Move to next line in VBA code
Replies
6
Views
4K
Fluff
Fluff

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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