Why is this VBA Code repeating itself?

V

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
Office Version
  1. 365
Platform
  1. Windows
Hello everyone! Trying to learn the basics of VBA codes. This one, I expected to run on the first 16 sheets of this worksheet, instead, it keeps repeating itself on the first one. Can anyone please correct it and give me a little explanation on what is wrong? Thanks!
VBA Code: 
Sub Copy_Paste_Apps_CA()
Dim i as Long
For i = 1 to 16
    Windows("zStats test.xlsx").Activate
    Range("G5:G42").Select
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("G5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("I5:I42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("I5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("K5:K42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("K5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("M5:M42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("M5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("G48:P52").Select
    Range("P52").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("G48").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Range("F5:Q42").Select
    Range("Q42").Activate
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42").Select
    Range("Q5").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("C44").Select
Next i
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
You just have "i" as a numeric counter from 1 to 16.
You do not have any correlation with that counter and any sheets.
So basically, you are just doing the exact same thing 16 times.
 
Upvote 0
ok thanks, so how can it be modified to run on sheets 1 to 16?
 
Upvote 0
Add this line under your for loop line:
Rich (BB code): 
For i = 1 to 16
    Sheets(i).Activate
    ...
 
Upvote 0
Solution
You are welcome!

Note that you can also clean up your code a bit too. It is usually not necessary to select ranges before working with them (the Macro Recorder records each of those steps literally).
So you can usually combine sections like this:
VBA Code: 
    Range("G5:G42").Select
    Selection.Copy
to this:
VBA Code: 
    Range("G5:G42").Copy

So where one row has a range and a "Select" or "Activate" followed by something that starts with "Selection." on the next line can usually combined into one line.

And when you have sections like this where you are doing a lot of things to the same range:
Excel Formula: 
 Range("Q42").Activate
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
You can use WITH clause instead, i.e.
Excel Formula: 
 With Range("Q42")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
 End With
See: With statement (VBA)

These clean-ups serve two purposes:
1. Shortens your code
2. Makes your code more efficient and faster ("selections" slow down your code).
 
Upvote 0
Joe4 said:
You are welcome!

Note that you can also clean up your code a bit too. It is usually not necessary to select ranges before working with them (the Macro Recorder records each of those steps literally).
So you can usually combine sections like this:
VBA Code: 
    Range("G5:G42").Select
    Selection.Copy
to this:
VBA Code: 
    Range("G5:G42").Copy

So where one row has a range and a "Select" or "Activate" followed by something that starts with "Selection." on the next line can usually combined into one line.

And when you have sections like this where you are doing a lot of things to the same range:
Excel Formula: 
 Range("Q42").Activate
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
You can use WITH clause instead, i.e.
Excel Formula: 
 With Range("Q42")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
 End With
See: With statement (VBA)

These clean-ups serve two purposes:
1. Shortens your code
2. Makes your code more efficient and faster ("selections" slow down your code).
Click to expand...
I see! Thanks for the tip! There is still a lot I need to learn! Lol!
 
Upvote 0
No problem!

Yes, the Macro Recorder is a great tool to get snippets of code, but is quite literal, and records every move you make, many of which can be combined or eliminated to make the code more efficient.
 
Upvote 0
Hi Joe! Happy new year! I hope you can help me with this one. I made this macro recording for this particular workbook named "Stats 2021-22". However, I need to have it work in multiple worksheets and I have to keep changing the worksheet name in order to make it work. So, my question is: is there a way to change the worksheet name so that it works in any worksheet regardless of the name? The worksheet name appears two times:
VBA Code: 
Sub Adding_G_P_2()
'
' Adding_G_P_2 Macro
'

'
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("A5:B42").Select
    Selection.FormatConditions.Delete
    Windows("zStats test.xlsx").Activate
    Range("A5:B42").Select
    Selection.Copy
    Windows("Stats 2021-22.xlsx").Activate
    Range("A5:B42").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D5").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("D5:D42"), Type:=xlFillDefault
    Range("D5:D42").Select
    Range("G5").Select
    Selection.AutoFill Destination:=Range("G5:G42"), Type:=xlFillDefault
    Range("G5:G42").Select
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:I42"), Type:=xlFillDefault
    Range("I5:I42").Select
    Range("K5").Select
    Selection.AutoFill Destination:=Range("K5:K42"), Type:=xlFillDefault
    Range("K5:K42").Select
    Range("M5").Select
    Selection.AutoFill Destination:=Range("M5:M42"), Type:=xlFillDefault
    Range("M5:M42").Select
    Range("O5:P5").Select
    Selection.AutoFill Destination:=Range("O5:P45"), Type:=xlFillDefault
    Range("O5:P45").Select
    Range("M43:R46").Select
    Range("R46").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42").Select
    Range("Q5").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("R5:DL43").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("BD50").Select
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("I:I,J:J,M:M,N:N").Select
    Range("N1").Activate
    Selection.EntireColumn.Hidden = True
    Range("C45").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 130
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 137
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 140
    Columns("EY:FL").Select
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 151
    ActiveWindow.ScrollColumn = 152
    Range("EY:FL,FZ:GL").Select
    Range("FZ1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 115
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    Range("DX:DX,DZ:DZ,EG:EG,EI:EI").Select
    Range("EI1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 36
    Columns("DP:DP").Select
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
End Sub
 
Upvote 0
valmir said:
Hi Joe! Happy new year! I hope you can help me with this one. I made this macro recording for this particular workbook named "Stats 2021-22". However, I need to have it work in multiple worksheets and I have to keep changing the worksheet name in order to make it work. So, my question is: is there a way to change the worksheet name so that it works in any worksheet regardless of the name? The worksheet name appears two times:
VBA Code: 
Sub Adding_G_P_2()
'
' Adding_G_P_2 Macro
'

'
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("A5:B42").Select
    Selection.FormatConditions.Delete
    Windows("zStats test.xlsx").Activate
    Range("A5:B42").Select
    Selection.Copy
    Windows("Stats 2021-22.xlsx").Activate
    Range("A5:B42").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D5").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("D5:D42"), Type:=xlFillDefault
    Range("D5:D42").Select
    Range("G5").Select
    Selection.AutoFill Destination:=Range("G5:G42"), Type:=xlFillDefault
    Range("G5:G42").Select
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:I42"), Type:=xlFillDefault
    Range("I5:I42").Select
    Range("K5").Select
    Selection.AutoFill Destination:=Range("K5:K42"), Type:=xlFillDefault
    Range("K5:K42").Select
    Range("M5").Select
    Selection.AutoFill Destination:=Range("M5:M42"), Type:=xlFillDefault
    Range("M5:M42").Select
    Range("O5:P5").Select
    Selection.AutoFill Destination:=Range("O5:P45"), Type:=xlFillDefault
    Range("O5:P45").Select
    Range("M43:R46").Select
    Range("R46").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42").Select
    Range("Q5").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("R5:DL43").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("BD50").Select
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("I:I,J:J,M:M,N:N").Select
    Range("N1").Activate
    Selection.EntireColumn.Hidden = True
    Range("C45").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 130
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 137
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 140
    Columns("EY:FL").Select
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 151
    ActiveWindow.ScrollColumn = 152
    Range("EY:FL,FZ:GL").Select
    Range("FZ1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 115
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    Range("DX:DX,DZ:DZ,EG:EG,EI:EI").Select
    Range("EI1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 36
    Columns("DP:DP").Select
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
End Sub
Click to expand...
Not two times, just once!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
333
RoryA
RoryA
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
503
kevin9999
K
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
526
jolivanes
jolivanes
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
105
willow1985
willow1985

Forum statistics

Threads
1,215,029
Messages
6,122,760
Members
449,095
Latest member
m_smith_solihull

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