![]() ![]() ![]() ![]() Option Explicit Sub Refresh_Data() Application.ScreenUpdating = False Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Project_Plan") sh.Unprotect "1234" Dim i As Long sh.Range("G3:XFD3").UnMerge sh.Range("G1:XFD3").Clear sh.Range("G1:XFD3").Orientation = 0 Dim lc, lr As Integer For i = (sh.Range("C:C")) To (sh.Range("D:D")) If sh.Range("G1").Value = "" Then sh.Range("G1").Value = i Else lc = sh.Range("XFD1").End(xlToLeft).Column sh.Cells(1, lc + 1).Value = i End If Next i lc = sh.Range("XFD1").End(xlToLeft).Column If sh.Range("C1").Value = "Daily" Then sh.Range("G3").Value = "=G1" sh.Range("G3", sh.Cells(3, lc)).FillRight sh.Range("E3").Copy sh.Range("G3", sh.Cells(3, lc)).PasteSpecial xlPasteFormats sh.Range("G3", sh.Cells(3, lc)).NumberFormat = "D-MMM" sh.Range("G3", sh.Cells(3, lc)).Orientation = 90 sh.Range("G3", sh.Cells(3, lc)).EntireColumn.ColumnWidth = 2.5 Else For i = 7 To lc Step 7 sh.Cells(3, i).Value = "Week-" & i / 7 sh.Range("E3").Copy sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).PasteSpecial xlPasteFormats sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).EntireColumn.ColumnWidth = 0.8 sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).Merge sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).HorizontalAlignment = xlCenter sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).VerticalAlignment = xlCenter Next i lc = sh.Range("XFD3").End(xlToLeft).Column + 6 End If lr = sh.Range("B" & ).End(xlUp).Row sh.Range("G1:XFD1").NumberFormat = "D-MMM-YY" sh.Range("G1:XFD1").Font.Color = VBA.vbWhite sh.Range("H4:XFD" & ).Clear sh.Range("G5:G" & ).Clear sh.Range("A" & lr + 1, "A" & ).EntireRow.Clear sh.Range("B:F").Locked = False sh.Range("G1:XFD3").Locked = True sh.Range("G1:XFD3").FormulaHidden = True sh.Range("G4:G" & sh.Range("B" & ).End(xlUp).Row).FillDown sh.Range("G4", sh.Cells(lr, lc)).FillRight With sh.Range("B3", sh.Cells(lr, lc)). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |