Badge Report Macro

[julievandermeulen]

I need help creating a macro to format a report I have to run every week at work. It takes 2 hours to format it. I have tried to download the XL2BB but it won't download. Is there another way to share a workbook?

 

[Fuji]

We basically think that you would run the proposed code from the workbook that you uploaded, not from PERSONAL.XLSB, so please mention it when you ask.
try change "test" sub procedure with

Sub test()
    Dim wb As Workbook, myAreas As Areas, r As Range, c As Range, s$, sp As Shape, HD()
    Dim a, i&, ws As Worksheet, myNames, dic As Object, xx As Single
    Set wb = ActiveWorkbook
    Set dic = CreateObject("Scripting.Dictionary")
    Set ws = wb.Sheets.Add
    ws.UsedRange.Clear
    For Each sp In ws.Shapes
        sp.Delete
    Next
    Set myAreas = wb.Sheets("master tab").Rows(3).SpecialCells(2).Areas
    ReDim myNames(1 To myAreas.Parent.Parent.UsedRange.Count), HD(1 To 2)
    HD(1) = myAreas(1).Cells(1): HD(2) = myAreas(1).Cells(2)
    For Each r In myAreas
        Set c = r.CurrentRegion
        a = c.Value
        For i = 3 To UBound(a, 2)
            ReDim Preserve HD(1 To UBound(HD) + 1): HD(UBound(HD)) = a(1, i)
        Next
        For i = 2 To UBound(a, 1)
            If a(i, 1) <> "" Then
                s = Join(Array(a(i, 1), a(i, 2)), Chr(2))
                If Not dic.exists(s) Then
                    Set dic(s) = c(i, 1).Resize(c(i, 1).MergeArea.Rows.Count, UBound(a, 2))
                    myNames(dic.Count) = s
                End If
            End If
        Next
    Next
    ReDim Preserve HD(1 To UBound(HD) + 1): HD(UBound(HD)) = "Notes"
    ReDim Preserve myNames(1 To dic.Count)
    mySort myNames, 1, UBound(myNames)
    GetAllData myAreas, HD, myNames, dic, ws, 4
End Sub

 

[julievandermeulen]

Sorry. I run it in my personal macro workbook. The report pulls into a huge spreadsheet and I copy and paste what I need into a spreadsheet.

I did what you suggested but get the same error.

 

[Fuji]

Are you testing on the workbook you uploaded?
If so, your Excel must be something wrong.

 

[julievandermeulen]

yes i am.

 

[julievandermeulen]

I have tried the macro on my work computer and i still get the same error. It gets hung up on this line.

 

[Fuji]

If you still get such error on that file I uploaded in #16, I can't help you anymore.
Don't run the code fromPERSONAL.XLSB.

 

[julievandermeulen]

Why does running it in personal.xlsb not work?

I tried to run it in all workbooks and get this error.

 

[Fuji]

Is the file in #16 working or not?

If not, it will never work from any workbook.

 

[julievandermeulen]

Is the file in #16 working or not?

If not, it will never work from any workbook.

will you tell me if there is a trick to opening the file in #16. I can't open it to even try it.

 

[Fuji]

I don't know as I can still download the file without problem.

Reg the picture in #27 of your post, it says "mySort" sub procedure is not in PERSONAL.XLMB.