Hi Gurus,
Looking for your help to fix my code below, what I am trying to do is to update my table vertically, instead of horizontally. Just to clarify, I was updating my data like this:
<tbody>
</tbody>
So, with my current code, the table will be updated in I1. This is the code:
Sub test()
'*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*
'Update Open Resolved Problems Table
Dim LastRow3 As Long
Worksheets("Open Resolved Problems").Activate
Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(0, 1).Column).Formula = "=Concatenate(TEXT(TODAY(),""yyyy""),""-"",WeekNum(Today())-1)"
Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(0, 0).Column).Activate
ActiveCell.Offset(1, 0).Formula = _
"=IF(C$1=""Open"",SUMPRODUCT(--ISNUMBER(SEARCH({""Assigned"",""New""},'Raw Data'!$E:$E))),IF(C$1=""Resolved"",COUNTIFS('Raw Data'!$E:$E,""Resolved with Permanent Fix"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1)),IF(C$1=""Duplicate/Rejected"",COUNTIFS('Raw Data'!$E:$E,""Duplicate"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1))+COUNTIFS('Raw Data'!$E:$E,""Rejected"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1)),IF(C$1=""Known Error"",COUNTIFS('Raw Data'!$E:$E,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1),'Raw Data'!$K:$K,""*Known*"")))))"
Selection.Borders.LineStyle = xlContinuous
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Color = vbWhite
Selection.Interior.ColorIndex = 1
ActiveCell.Offset(1, 0).Copy
LastRow3 = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveCell.Offset(1, 0)
.Resize(LastRow3 - .Row + 1).PasteSpecial xlPasteAll
Selection.Borders.LineStyle = xlContinuous
Selection.HorizontalAlignment = xlCenter
End With
'Copy values only
Columns("A:XFD").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
'*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*
End Sub
However, I'd like to show the data differently:
<tbody>
</tbody>
So I'd like my table to be updated next in A7.
Appreciate any assistance.
****** id="cke_pastebin" style="position: absolute; top: 988.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody>
Looking for your help to fix my code below, what I am trying to do is to update my table vertically, instead of horizontally. Just to clarify, I was updating my data like this:
A | B | C | D | E | F | G | |
1 | 2018-30 | 2018-31 | 2018-32 | 2018-33 | 2018-34 | 2018-35 | |
2 | Open | 27 | 26 | 25 | 27 | 28 | 30 |
3 | Resolved | 2 | 1 | 1 | 4 | 2 | 0 |
4 | Duplicate | 0 | 0 | 1 | 0 | 1 | 0 |
5 | Known | 0 | 0 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
So, with my current code, the table will be updated in I1. This is the code:
Sub test()
'*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*
'Update Open Resolved Problems Table
Dim LastRow3 As Long
Worksheets("Open Resolved Problems").Activate
Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(0, 1).Column).Formula = "=Concatenate(TEXT(TODAY(),""yyyy""),""-"",WeekNum(Today())-1)"
Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(0, 0).Column).Activate
ActiveCell.Offset(1, 0).Formula = _
"=IF(C$1=""Open"",SUMPRODUCT(--ISNUMBER(SEARCH({""Assigned"",""New""},'Raw Data'!$E:$E))),IF(C$1=""Resolved"",COUNTIFS('Raw Data'!$E:$E,""Resolved with Permanent Fix"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1)),IF(C$1=""Duplicate/Rejected"",COUNTIFS('Raw Data'!$E:$E,""Duplicate"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1))+COUNTIFS('Raw Data'!$E:$E,""Rejected"",'Raw Data'!$K:$K,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1)),IF(C$1=""Known Error"",COUNTIFS('Raw Data'!$E:$E,CONCATENATE(YEAR(TODAY()),""-"",WEEKNUM(TODAY())-1),'Raw Data'!$K:$K,""*Known*"")))))"
Selection.Borders.LineStyle = xlContinuous
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Color = vbWhite
Selection.Interior.ColorIndex = 1
ActiveCell.Offset(1, 0).Copy
LastRow3 = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveCell.Offset(1, 0)
.Resize(LastRow3 - .Row + 1).PasteSpecial xlPasteAll
Selection.Borders.LineStyle = xlContinuous
Selection.HorizontalAlignment = xlCenter
End With
'Copy values only
Columns("A:XFD").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
'*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*
End Sub
However, I'd like to show the data differently:
A | B | C | D | E | |
1 | WW | Open | Resolved | Duplicate | Known |
2 | 2018-30 | 27 | 2 | 0 | 0 |
3 | 2018-31 | 26 | 1 | 0 | 0 |
4 | 2018-32 | 25 | 1 | 1 | 0 |
5 | 2018-33 | 27 | 4 | 0 | 0 |
6 | 2018-34 | 28 | 2 | 1 | 0 |
7 | 2018-35 | 30 | 0 | 0 | 0 |
8 | |||||
9 | |||||
10 |
<tbody>
</tbody>
So I'd like my table to be updated next in A7.
Appreciate any assistance.
****** id="cke_pastebin" style="position: absolute; top: 988.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
Duplicate |
<tbody>
</tbody>