macros and hidden sheets

[mani_singh]

ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!

any ideas how i can hide them and have the macro run ?

 

[mani_singh]

ok here's the code for the macro


Sub pick()
'
' Macro
'

Sheets("running").Select

Application.ScreenUpdating = False
Sheets("Baseline Data").Select
Range("H2:H1500").Select
Selection.Copy
Sheets("Hidden").Select
Range("A2").Select
ActiveSheet.Paste

Dim Limit As Long
Dim c As Long
Dim d As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
d = 2
For c = 2 To Limit
If WorksheetFunction.CountIf(Range("C:C"), Cells(c, 1)) = 0 Then
Cells(d, 3) = Cells(c, 1)
d = d + 1
End If
Next c

Range("E2").Select
Selection.sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("C2:D41").Select
Selection.Copy
Range("H8").Select
ActiveSheet.Paste
Range("I8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-6]C[-5]"
Range("I8").Select
Selection.AutoFill Destination:=Range("I8:I47"), Type:=xlFillDefault
Range("I8:I47").Select

Sheets("Rolling").Select
Range("Y1:AE41").Select
Selection.Cut
Range("A43").Select
ActiveSheet.Paste
Range("A1:W41").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("A43:G83").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Range("B2:F41").Select
Selection.ClearContents

Sheets("Tier Control").Select
Range("A2:A41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Rolling").Select
Range("B2").Select
ActiveSheet.Paste


Sheets("Tier Control").Select
Range("C2:G41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Rolling").Select
Range("C2").Select
ActiveSheet.Paste

ActiveWindow.SmallScroll Down:=-9
Range("B2:F41").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("C2").Select

Sheets("Baseline Data").Select
Columns("H:H").Select
Selection.Copy
Sheets("tier control").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("Baseline Data").Select
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("tier control").Select
Columns("B:B").Select
ActiveSheet.Paste

Dim ColLimit As Long
Dim RowLimit As Long
Dim co As Long
Dim r As Long
Dim PersonArray(3) As Long
RowLimit = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To RowLimit
Cells(r, 3) = 0
Cells(r, 4) = 0
Cells(r, 5) = 0
Cells(r, 6) = 0
For co = 2 To RowLimit
If Cells(r, 1) = Cells(co, 1) Then
Select Case Cells(co, 2)
Case 0
Cells(r, 3) = Cells(r, 3) + 1
Case 1
Cells(r, 4) = Cells(r, 4) + 1
Case 2
Cells(r, 5) = Cells(r, 5) + 1
Case 3
Cells(r, 6) = Cells(r, 6) + 1
End Select
End If
Next co
Next r
For r = RowLimit To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & r), Range("A" & r)) > 1 Then
Rows(r).Delete shift:=xlUp
End If
Next r

Range("G2").Select
Selection.sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("H2").Select

Sheets("tier control").Select
Range("A2:A41").Select
Selection.Copy
Sheets("Fixed@Tier").Select
Range("B3").Select
ActiveSheet.Paste
Sheets("tier control").Select
Range("D2:G41").Select
Application.CutCopyMode = False
Selection.Copy
Range("G45").Select
Sheets("Fixed@Tier").Select
Range("C3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9

Range("B2:F43").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A43:F43").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("G17").Select

Sheets("Menu").Select
End Sub

 

[erik.van.geit]

here is the first half
you should be able to continue (starting from "Dim ColLimit As Long")
not sure if everything is correct for 100%, but you will get the idea

search VBAhelpfiles for "with ... end with"
take care of the DOTs (possibly I forgot some)

please use the CODEtags when posting code

Sub pick()
Dim Limit As Long
Dim c As Long
Dim d As Long

Sheets("running").Select '''''''purpose??

Application.ScreenUpdating = False
'already written by Richard
    With Sheets("Baseline Data")
    .Range("H2:H1500").Copy Sheets("Hidden").Range("A2")
    
    Limit = .Cells(Rows.Count, 1).End(xlUp).Row
    d = 2
        For c = 2 To Limit
            If WorksheetFunction.CountIf(.Range("C:C"), .Cells(c, 1)) = 0 Then
            .Cells(d, 3) = .Cells(c, 1)
            d = d + 1
            End If
        Next c
    
    .Range("E2").Sort Key1:=.Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    .Range("C2:D41").Copy Range("H8")
    
        With .Range("I8")
        .FormulaR1C1 = "=R[-6]C[-5]"
        .AutoFill Destination:=.Range("I8:I47"), Type:=xlFillDefault
        End With
    End With

    With Sheets("Rolling")
    .Range("Y1:AE41").Cut .Range("A43")
    .Range("A1:W41").Cut .Range("I1")
    .Range("A43:G83").Cut .Range("A1")
    .Range("B2:F41").ClearContents
    End With

    With Sheets("Tier Control")
    .Range("A2:A41").Copy Sheets("Rolling").Range("B2")
    .Range("C2:G41").Copy Sheets("Rolling").Range("C2")
    End With

    With Sheets("Rolling")
        With .Range("B2:F41").Select
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            End With
        End With
    Range("C2").Select
    End With

    With Sheets("Baseline Data")
    .Columns("H:H").Copy Sheets("tier control").Columns("A:A")
    .Columns("P:P").Copy Sheets("tier control").Columns("B:B")
    End With

 

[mani_singh]

thanks guys! appriciate this

 

[erik.van.geit]

you're welcome!

just another thought
I think you can replace the 4 x 5 lines of top, bottom, left, right

            With .Borders
            .LineStyle = xlContinuous 
            .Weight = xlThin 
            .ColorIndex 
            End With

(hoping there are no typos, but you get the picture )