Hello there,
Also, theres a lot of hidden rows on the "temp_data" page that I cant unhide?
Add the following line of code:
Sheets(2).UsedRange.AutoFilter
Before the end sub and last line of code, so that the entire code look like so:
Sub FormatReport()
'declare variables
Dim x As Long, y As Long, z As Long, a As Long, b As Long, c As Range, col As Long, rw As Long
Dim t As Long, u As Long, v As Long, LR As String, i As Long, rng As Range
ResetReport 'run the reset report macro that clears the current report
a = 6 'set a to row 6 as this is the empty row below the first three categories
b = 9 'set b to row 9 as this is the empty row below the second categories
With Sheets(2) 'with the second worksheet in the workbook
.Select 'select the workbook
LR = .Range("A6555").End(xlUp).Row 'set LR equal to the last row in column A that contains a value
'set the below variables to the number of cells in each
'defined columns whose value was greater than 0
x = WorksheetFunction.CountIf(.Range("B1:B" & LR), ">0")
y = WorksheetFunction.CountIf(.Range("c1:c" & LR), ">0")
z = WorksheetFunction.CountIf(.Range("d1:d" & LR), ">0")
t = WorksheetFunction.CountIf(.Range("e1:e" & LR), ">0")
u = WorksheetFunction.CountIf(.Range("f1:f" & LR), ">0")
v = WorksheetFunction.CountIf(.Range("g1:g" & LR), ">0")
'the below two lines variables will be used to know how many rows to insert below the first
'and second line of categories
x = WorksheetFunction.Max(x, y, z) 'reset x to the larger value of x, y, z
y = WorksheetFunction.Max(t, u, v) 'reset y to the larger value of t, u , v
With Sheets(1) 'with the first worksheet in the workbook
.Rows("6:6").Copy 'copy row 6
.Rows("6:" & x + 6).Insert shift:=xlDown 'insert the number of rows defined by x above
b = b + x + 1 'reset b (set originally as row 9) to the new row where the second categories start
.Rows(b & ":" & b).Copy 'copy row b
.Rows(b & ":" & b + t).Insert shift:=xlDown 'insert the number of rows defined by t above
End With 'end with the first worksheet in the workbook
For i = 2 To 7 'loop through numbers 2 through 7, i will represent the current number in the loop
'2 through 7 represent the column number 2 being B and 7 being G
With .UsedRange 'with the used range in the second worksheet in the workbook
.AutoFilter 'autofilter the range
.AutoFilter Field:=i, Criteria1:="<>0" 'filter the current column number (i) in the loop
'to all values that are not equal to 0
End With 'end with the usedrange
For Each c In .Range("A2:A" & LR).Cells 'loop through cells A2 to A and the last Row
If c.RowHeight > 0 Then 'if the row is not hidden then
If rng Is Nothing Then 'if no range have been assigned to the variable rng then
Set rng = c 'define rng as cell c
Else: Set rng = Union(rng, c) 'if is has been defined then add c to the existing range
End If
End If
Next c 'move to next cell in the loop
rng.Select 'select the rng (defined range)
Selection.Copy 'copy rng
'the below statement will tell where to paste the values copied
'based on the current column (i) in the i loop
Select Case i 'if the current i in the loop is...
Case 2 '2 (column B) Diamond then
col = 1 'set col equal to 1 (column A in the Diamond Date worksheet)
rw = 6 'set rw to 6 then row to paste to in the Diamond Data worksheet
Case 3
col = 5
rw = 6
Case 4
col = 9
rw = 6
Case 5
col = 1
rw = b
Case 6
col = 5
rw = b
Case 7
col = 9
rw = b
End Select
'paste the copied values into the first worksheet in the workbook
'into cell rw (defined above as a row number) and column (col defined above)
Sheets(1).Cells(rw, col).PasteSpecial xlPasteValues
Set rng = Nothing 'clear the rng's assigned to rng
'loop through cell's from row 2 to the last row in the current column (i) in the i loop
For Each c In .Range(.Cells(2, i), .Cells(LR, i)).Cells
If c.RowHeight > 0 Then 'if the row is not hiddent then
If rng Is Nothing Then 'if no range have been assigned to the variable rng then
Set rng = c 'define rng as cell c
Else: Set rng = Union(rng, c) 'if is has been defined then add c to the existing range
End If
End If
Next c 'move to next cell in the new c loop
rng.Select 'select the rng (defined range)
Selection.Copy 'copy rng
'paste the copied values into the first worksheet in the workbook
'into cell rw (defined above as a row number) and one over from the col(column defined above)
Sheets(1).Cells(rw, col + 1).PasteSpecial xlPasteValues
Set rng = Nothing 'clear the rng's assigned to rng
Next i 'move to next column (i) in the i loop
End With
Application.CutCopyMode = False 'undo the copy mode
Sheets(2).UsedRange.AutoFilter
Sheets(1).Select 'select the first worksheet in the workbook (in this case Diamond Data)
End Sub
As for the formula what was the formula in the percentage column supposed to be?
Thanks!
Bookmarks