Excel 2007 : Excel 2007 Macro not running when click on View and run the Macro method

[excelex]

Hi All,

I have a excel 2007 work sheet having a Macroruns fine from VBA code and I debug or run it where as when I dont open the VBA code and just run the Macro it just created the Header of the work sheet and not the other records. Please help.

[romperstomper]

I think we would have to see the code. (please don't forget to use Code tags when posting)

[excelex]

Dim Count As Long
Dim Val1
Dim Val2
Dim Val3
Dim RowVal As Long
Dim Max As Long

Max = Sheets(SheetNum).UsedRange.Rows.Count
 
'Loop checks rows until match is found.
For Count = 1 To Max
    Val1 = WorksheetFunction.Index(Sheets(SheetNum).Range("A:C"), Count, offCol)
    Val2 = WorksheetFunction.Index(Sheets(SheetNum).Range("A:C"), Count, descpCol)
    Val3 = WorksheetFunction.Index(Sheets(SheetNum).Range("A:C"), Count, hrsCol)
    
    If Val1 = offVal And Val2 = descpVal And Val3 = hrsVal Then
        RowVal = Count
        Exit For
    End If
Next Count

GetRowNumber = RowVal
        
End Function
 Sub getUniqueValues(ByRef iArr() As String, ByRef dataWS As Worksheet, ByVal pos As Integer)

Dim i As Integer, j As Integer, n As Integer, x As String

 n = 1
    ReDim iArr(1 To n)
    iArr(1) = dataWS.Cells(2, pos)
    i = 2
    While i < (dataWS.UsedRange.Rows.Count + 1)
     If Not IsEmpty(dataWS.Cells(i, pos)) Then
        x = dataWS.Cells(i, pos)
        If Not IsEmpty(x) Then
            If IsError(Application.Match(x, iArr, 0)) Then
                n = n + 1
                ReDim Preserve iArr(1 To n)
                iArr(n) = x
            End If
        End If
         End If
        i = i + 1
       
    Wend

End Sub

Sub CreatePivotData()

    Dim SummaryTable As Worksheet
    Dim OutRow As Long
    Dim i As Integer, j As Integer, n As Integer, k As Integer
    Dim Arr() As String
    Dim ProjOff As String
        
    Dim off() As String, descp() As String, hrs() As String, periods As Long
    Dim offrow() As Long, descprow As Long, hrsrow As Long, a As Long
    
    On Error Resume Next
    'Set SummaryTable = ActiveCell.CurrentRegion
    Set SummaryTable = Worksheets("2b-ABCD")
    
    
    ReDim Arr(SummaryTable.Rows.Count, 6)
    ReDim offrow(0 To (SummaryTable.UsedRange.Rows.Count * 3))
    
    OutRow = 0
       
    
    ProjOff = ""
    Application.ScreenUpdating = False
    
    getUniqueValues iArr:=off, dataWS:=SummaryTable, pos:=1
    getUniqueValues iArr:=descp, dataWS:=SummaryTable, pos:=2
    getUniqueValues iArr:=hrs, dataWS:=SummaryTable, pos:=3
    periods = SummaryTable.Range("E1:BX1").Count + 2
    
    'Set periodrow = SummaryTable.Range("E1:BT1")
    
    a = 0
    For i = LBound(off) To UBound(off)
      For n = LBound(descp) To UBound(descp)
       For k = LBound(hrs) To UBound(hrs)
            'Get the row number for given office, discipline and Hours.
            offrow(a) = GetRowNumber(off(i), 1, descp(n), 2, hrs(k), 3, 6)
             
            a = a + 1
            Next k
       Next n
    Next i
     

    For j = 6 To periods
        If SummaryTable.Cells(1, j) = "" Then
        
        Else
            For a = LBound(offrow) To UBound(offrow)
            If offrow(a) <> 0 Then
                'See if its a new row.
                
                Arr(OutRow, 0) = SummaryTable.Cells(offrow(a), 1)
                Arr(OutRow, 1) = SummaryTable.Cells(offrow(a), 2)
                Arr(OutRow, 2) = SummaryTable.Cells(1, j)
                Arr(OutRow, (a Mod 3) + 3) = CLng(SummaryTable.Cells(offrow(a), j))
                ' Arr(OutRow, (a Mod 3) + 3) = CLng(SummaryTable.Cells(offrow(a), j))
                
                'increment the row after getting the planned, earned and actual.
                If a Mod 3 = 2 Then
                    OutRow = OutRow + 1
                End If
                
            End If
            Next a
        End If
    Next j
    
    With Worksheets(10)
    .Name = "ABC_DETAILS"
    .Range("A1:F1") = Array("ABC", "DEF", "GHI", "JKL", "MNO", "PQR")
    .Range(Cells(2, 1), Cells(UBound(Arr, 1), UBound(Arr, 2))).Value = Arr
    .Range("D2:D65000").NumberFormat = "#,##0_);(#,##0)"
    .Range("E2:E65000").NumberFormat = "#,##0_);(#,##0)"
    .Range("F2:F65000").NumberFormat = "#,##0_);(#,##0)"
    End With
    
    Erase Arr()
End Sub

[excelex]

Thank you for your response, I am using the above VBA method to populate the new worksheet but when I click on View -> Macro and select the method and run it just populated the header of the array. Please help.

[romperstomper]

This line:

.Range(Cells(2, 1), Cells(UBound(Arr, 1), UBound(Arr, 2))).Value = Arr

should be:

.Range(.Cells(2, 1), .Cells(UBound(Arr, 1), UBound(Arr, 2))).Value = Arr

Note the use of code tags and please use them yourself in future when posting code.

[excelex]

Thanks a lot for your qick response butworks for me but not for others, Please advice if I am missing smething. It should also work on the development server

[excelex]

I found the issue, I am passing the tab # as a static value to the methods, please could you help me find the tab # of the named worksheet so that I pass that # to the method. Thanks a lot!

[romperstomper]

I'm not sure what you mean?