Recorded macro incorrectly hiding columns

R

Rusty315

Board Regular
Joined
Sep 13, 2012
Messages
54
Hi

I'm hoping someone can help me before I set my PC alight and send it hurtling to a fiery doom out one of our office windows.

I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.

The macro is as follows (sorry about the length):

Code: 
Sub COPY()
'
' COPY Macro
'

'
    Application.ScreenUpdating = False
    Columns("K:BT").Select
    Selection.EntireColumn.Hidden = False
    Range("M5:N24").Select
    Selection.COPY
    Range("O5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S5:T24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("U5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Y5:Z24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AA5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AE5:AF24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AG5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AK5:AL24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AM5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=19
    Range("AQ5:AQ24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AR5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AT5:AT24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AU5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AW5:AW24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("AX5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AX5:AX24").Select
    Application.CutCopyMode = False
    Range("AY5:AY24").Select
    Selection.COPY
    Range("AZ5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BA5:BA24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BB5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BC5:BC24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BD5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BE5:BE24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BF5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BG5:BG24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BH5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BI5:BI24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BJ5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BK5:BK24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BL5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BN5:BN24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BO5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BP5:BP24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BQ5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BR5:BR24").Select
    Application.CutCopyMode = False
    Selection.COPY
    Range("BS5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("M:N").Select
    Selection.EntireColumn.Hidden = True
    Columns("S:T").Select
    Selection.EntireColumn.Hidden = True
    Columns("Y:Z").Select
    Selection.EntireColumn.Hidden = True
    Columns("AE:AF").Select
    Selection.EntireColumn.Hidden = True
    Columns("AK:AL").Select
    Selection.EntireColumn.Hidden = True
    Columns("AQ:AQ").Select
    Selection.EntireColumn.Hidden = True
    Columns("AT:AT").Select
    Selection.EntireColumn.Hidden = True
    Columns("AW:AW").Select
    Selection.EntireColumn.Hidden = True
    Columns("AY:AY").Select
    Selection.EntireColumn.Hidden = True
    Columns("BA:BA").Select
    Selection.EntireColumn.Hidden = True
    Columns("BC:BC").Select
    Selection.EntireColumn.Hidden = True
    Columns("BE:BE").Select
    Selection.EntireColumn.Hidden = True
    Columns("BG:BG").Select
    Selection.EntireColumn.Hidden = True
    Columns("BI:BI").Select
    Selection.EntireColumn.Hidden = True
    Columns("BK:BK").Select
    Selection.EntireColumn.Hidden = True
    Columns("BN:BN").Select
    Selection.EntireColumn.Hidden = True
    Columns("BP:BP").Select
    Selection.EntireColumn.Hidden = True
    Columns("BR:BR").Select
    Selection.EntireColumn.Hidden = True
    Range("D3:J3").Select
    Application.ScreenUpdating = True
End Sub

I have no idea why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.

If anyone can offer any help, I'd be very appreciative.

Cheers
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Here, you have hidden everything from K:BT --
Code: 
    Columns("K:BT").Select     Selection.EntireColumn.Hidden = False

What do you want hidden, and what should be visible, when you finish?

Denis
</pre>
 
Upvote 0
Your entire code could be condensed to a few rows...

Code: 
Sub COPY()
Dim i As Long
Application.ScreenUpdating = False
    
Columns("K:BT").EntireColumn.Hidden = False
    
For i = 13 To 70 Step 6
    With Range(Cells(5, i), Cells(24, i + 1))
        .COPY
        .EntireColumn.Hidden = True
        Cells(5, i + 2).PasteSpecial xlValues
    End With
Next i
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi SydneyGeek & njimack, thanks for your help.

njimack, thanks for the macro. The columns that are unhidden, copied and hidden again aren't in a particular pattern. Sometimes it'll be every second column that needs to be copied, sometimes every third column. Your macro is very close to doing that but unfortunately, because the columns aren't in a pattern, the macro is copying some columns that should only contain values and pasting those over formulas. I do appreciate you writing that for me, though. Much more elegant and simple that my 6,000 lines of code!

SydneyGeek, the bit at the start where I unhide columns K to BT is because the columns where I'm copying and pasting from are normally hidden. The reason I'm copying and pasting values is because I'm using INDIRECT formulas and they only work when the workbooks they refer to are open. What I've done is set up this macro so that when the workbooks that the INDIRECT formulas refer to are open, users can click a button and the macro will copy the values produced by the INDIRECT formula into other cells so that when the other workbook is closed again, the values remain instead of the #REF! error that normally appear with INDIRECT formulas. The other users of this workbook are not familiar with Excel functions in the least so I want the INDIRECT formulas to remain hidden.

The columns that I need to unhide, copy from then hide again are M, N, S, T, Y, Z, AE, AF, AK, AL, AQ, AT, AW, AY, BA, BC, BE, BG, BI, BK, BN, BP and BR.

The unhide and copy/paste portions of the macro I recorded are working fine. It's just that when the columns are hidden again, they all get hidden again (no problem) but so do columns K to AN (that's my problem).

Again, really appreciate your help!
 
Upvote 0
Another option that could save you hiding and unhiding:

Have a sheet that contains all the INDIRECT formulas
A second sheet is a copy of the first, just pasting values from the first sheet.
Hide the first sheet.

Then your code boils down to something like:

Code: 
    Sheets("FormulaSheet").Activate 'you can activate a hidden sheet, but you can't select a hidden sheet
    ActiveSheet.Range("C4:AE24").Copy
    Sheets("ValueSheet").Activate
    Range("C4").PasteSpecial Paste:=xlPasteValues

Denis
 
Upvote 0
That's an excellent solution, much more simple. It's so obvious that I feel stupid for not having thought of it. Thanks for your help, SydneyGeek, it's very much appreciated!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
189
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
245
willow1985
willow1985
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
97
Alex Blakenburg
A
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
99
Northern NY Design
N
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
343
jolivanes
jolivanes

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

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