Macro with hidden Sheets

[dazfoz]

Hi,

Newbie here, I have a Macro that I have recorded to copy and paste some data into a sheet, which runs just fine, until I hide a couple of worksheets. I have searched and found that I shouldnt select the worksheets?!? How do I not select what I need to copy?

I'm also trying to make it so that the list of column autosizing that I have at the end is in one command, I have tried changing columns("D:D").entirecolumn.autofit to columns("D:D", "G:G").entirecolumn.autofit, but this throws up an error, so any help on this as well would be greatly appreciated.

Plus if you see anything else that can be simplified feel free to shout!

Sheets("Paste Special").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("Lookup").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Paste Special").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Paste Special").Select
    ActiveWindow.SmallScroll Down:=-54
    Sheets("Pivot").Select
    Range("C6").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Columns("B:B").ColumnWidth = 14.14
    Columns("C:C").ColumnWidth = 12.86
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").ColumnWidth = 12.86
    Columns("F:F").ColumnWidth = 14
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").ColumnWidth = 15.14
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Columns("K:K").ColumnWidth = 14.57
    Columns("L:L").ColumnWidth = 14.29
    Columns("M:M").EntireColumn.AutoFit
    Columns("N:N").EntireColumn.AutoFit
    Columns("O:O").EntireColumn.AutoFit
    Columns("P:P").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("R:R").EntireColumn.AutoFit
    Columns("S:S").EntireColumn.AutoFit
    Columns("T:T").EntireColumn.AutoFit
    Range("B5").Select
    ActiveWindow.FreezePanes = True

Thanks in advance from an amateur.

 

[rs2k]

Columns("B:T").EntireColumn.AutoFit

Should get you started, also look at trying not to use 'select' statements.

Off home now, I will have another look in about an hour,

HTH
Colin

 

[njimack]

You don't need to select sheets or cells to perform actions on them. Here's a cleaned up version of your code (untested) that should run a lot faster...

Sub test()
Application.ScreenUpdating = False
Dim LR As Long
Dim LC As Long
Sheets("Paste Special").Cells.ClearContents
 
With Sheets("Lookup")
    LR = .Range("A3").End(xlDown).Row
    LC = .Range("A3").End(xlToRight).Column
 
    Range(.Cells(3, 1), Cells(LR, LC)).Copy
 
    Sheets("Paste Special").Range("A1").PasteSpecial xlValues
    Sheets("Paste Special").Range("A1").PasteSpecial xlFormats
 End With
 
    With Sheets("Pivot")
        .PivotTables("PivotTable1").PivotCache.Refresh
        .Columns("B:B").ColumnWidth = 14.14
        .Columns("C:C").ColumnWidth = 12.86
        .Columns("D:E").EntireColumn.AutoFit
        .Columns("F:F").ColumnWidth = 14
        .Columns("G:G").EntireColumn.AutoFit
        .Columns("H:H").ColumnWidth = 15.14
        .Columns("I:J").EntireColumn.AutoFit
        .Columns("K:K").ColumnWidth = 14.57
        .Columns("L:L").ColumnWidth = 14.29
        .Columns("M:T").EntireColumn.AutoFit
    End With
Application.ScreenUpdating = True
End Sub

 

[rs2k]

Pivot tables...! didn't see that one

 

[Smitty]

I have searched and found that I shouldnt select the worksheets?!? How do I not select what I need to copy?

Generally, when you see Select followed by Selection you can eliminate both and concatenate the remaining code.

E.G.

Sheets("Paste Special").Cells.ClearContents
    Sheets("Lookup").Range(Range([A3], Cells(Rows.Count, "A").End(xlUp)), Range([A3], Cells(1, Columns.Count).End(xlToLeft))).Copy
    
With Sheets("Paste Special").Range("A1")
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteValues
    End With

HTH,

 

[wigi]

My contribution:

Sub CleanUp()
    
    Sheets("Paste Special").Cells.ClearContents
    
    Sheets("Lookup").Range("A3", Sheets("Lookup").Range("A3").End(xlToRight).End(xlDown).End(xlDown)).Copy
    With Sheets("Paste Special")
        .Range("A1").PasteSpecial xlPasteFormats
        .Range("A1").PasteSpecial xlPasteValues
    End With
    
    With Sheets("Pivot")
        .PivotTables("PivotTable1").PivotCache.Refresh
        .Columns("D:T").AutoFit
        For i = 1 To 7
            .Columns(Choose(i, 2, 3, 5, 6, 8, 11, 12)).ColumnWidth = Choose(i, 14.14, 12.86, 12.86, 14, 15.14, 14.57, 14.29)
        Next
        
    End With
    
    ActiveWindow.FreezePanes = True

End Sub

Please test it out on a copy of your file - I'm unsure since I do not see your real file in front of me.

 

[dazfoz]

Njimack,

Thanks almost worked, it was just throwing up an error with the

Range(.Cells(3, 1), Cells(LR, LC)).Copy

Hope you are safe with all this crap going on in London

Wigi,

Thanks very much, worked a treat.

Smitty,

Thanks, for explaining that, a question though, why would we use select & selection in a macro if the function can be performed without actually selecting the sheet/cell?


Daz

 

[wigi]

Smitty,

Thanks, for explaining that, a question though, why would we use select & selection in a macro if the function can be performed without actually selecting the sheet/cell?

That's the point - do NOT use it !
It is not needed and slows down the code.

Working with Selection happens if the user is allowed to make a custom selection, which is then colored (for instance). You do not know upfront what range you will color.

 

[dazfoz]

OK..

Like most things in life then...useless crap that we dont really need!

Thanks all.

 

[Smitty]

Smitty,

Thanks, for explaining that, a question though, why would we use select & selection in a macro if the function can be performed without actually selecting the sheet/cell?

Selecting is a by product of the macro recorder, which by its nature is a literal recorder, so it literally records everything you do. Unfortunately, the recorder is non-deterministic, so it has to record at the level of the lowest common event, which is to select every object before taking an action on it. That's why you'll also see screen navigation get recorded. Excel can't tell that you don't really want to scroll, but just end up at AA2000, so it takes it into account.