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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2018-30[/TD]
[TD]2018-31[/TD]
[TD]2018-32[/TD]
[TD]2018-33[/TD]
[TD]2018-34[/TD]
[TD]2018-35[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]25[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Resolved[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Duplicate[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Known[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WW[/TD]
[TD]Open[/TD]
[TD]Resolved[/TD]
[TD]Duplicate[/TD]
[TD]Known[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2018-30[/TD]
[TD]27[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2018-31[/TD]
[TD]26[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2018-32[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2018-33[/TD]
[TD]27[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2018-34[/TD]
[TD]28[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2018-35[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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;">[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2018-30[/TD]
[TD]2018-31[/TD]
[TD]2018-32[/TD]
[TD]2018-33[/TD]
[TD]2018-34[/TD]
[TD]2018-35[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]25[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Resolved[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Duplicate[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Known[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WW[/TD]
[TD]Open[/TD]
[TD]Resolved[/TD]
[TD]Duplicate[/TD]
[TD]Known[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2018-30[/TD]
[TD]27[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2018-31[/TD]
[TD]26[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2018-32[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2018-33[/TD]
[TD]27[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2018-34[/TD]
[TD]28[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2018-35[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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;">[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]