Auto Write Name and Merge across
In Worksheet 'Defined Fiscal Weeks-DND' if have a table that defines the
fiscal calendar. Weeks of the Year Defined By Formula Week# Week Name Week Start Week End Week Statement 01 Week01 02-Jan-2009 08-Jan-2009 02-Jan to 08-Jan 02 Week02 09-Jan-2009 15-Jan-2009 09-Jan to 15-Jan 03 Week03 16-Jan-2009 22-Jan-2009 16-Jan to 22-Jan In worksheet 'Daily Entries Jan to Aug' is a calendar from jan to aug displaying day, date and month. Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 In worksheet 'Daily Entries Sep to Dec' is a calendar from Sep to Dec displaying day, date and month. Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 How can i script the weekname across every 7 days and automatically go to Sep to Dec sheet and continue. Also in the 'Defined Fiscal Weeks-DND' i have define the following: Start Col# End Col# Start Workbook location WeekName Range location 7 13 $G$3 ThisWorkbook.Worksheets("Daily Entries Jan to Aug").Range(''$G$3'') $G$3:$M$3 14 20 $N$3 ThisWorkbook.Worksheets("Daily Entries Jan to Aug").Range(''$N$3'') $N$3:$T$3 21 27 $U$3 ThisWorkbook.Worksheets("Daily Entries Jan to Aug").Range(''$U$3'') $U$3:$AA$3 The problem I am experiencing is that it will not jump to worksheet 'Daily Entries Sep to Dec' and continue to week 36, 37, 38, ......... ============================= This is the script below: ============================= Sub WeekGroup() ' ' test Macro ' Macro recorded 7/8/2009 by kevin.johnson ' ' ' ' These variables are used to defined which worksheet to process! '<<=============================================== ===================== Dim Sheetname01 As String Dim Sheetname02 As String Dim Sheetname03 As String Dim Sheetname04 As String Dim Sheetname05 As String Dim Sheetname06 As String Dim Sheetname07 As String Dim Sheetname08 As String Dim Sheetname09 As String Dim Sheetname10 As String Dim Sheetname11 As String Dim Sheetname12 As String Dim Sheetname13 As String Dim Sheetname14 As String Dim Sheetname15 As String Dim Sheetname16 As String Dim Sheetname17 As String Dim Sheetname18 As String Dim Sheetname19 As String Dim Sheetname20 As String Dim Sheetname21 As String Dim Sheetname22 As String Dim Sheetname23 As String Dim Sheetname24 As String Dim Sheetname25 As String Dim Sheetname26 As String Dim Sheetname27 As String Dim Sheetname28 As String Dim Sheetname29 As String Dim Sheetname30 As String Dim Sheetname31 As String Dim Sheetname32 As String Dim Sheetname33 As String Dim Sheetname34 As String Dim Sheetname35 As String Dim Sheetname36 As String ' Dim Sheetname37 As String ' Dim Sheetname38 As String ' Dim Sheetname39 As String ' Dim Sheetname40 As String ' Dim Sheetname41 As String ' Dim Sheetname42 As String ' Dim Sheetname43 As String ' Dim Sheetname44 As String ' Dim Sheetname45 As String ' Dim Sheetname46 As String ' Dim Sheetname47 As String ' Dim Sheetname48 As String ' Dim Sheetname49 As String ' Dim Sheetname50 As String ' Dim Sheetname51 As String ' Dim Sheetname52 As String ' Dim Sheetname53 As String '<<=============================================== ===================== Dim JantoAug As String Dim SeptoDec As String Dim WeekAllJantoAug As String Dim WeekAllSeptoDec As String ' These are used to define the range to insert the week name! '<<=============================================== ===================== Dim WeekName01 As String Dim WeekName02 As String Dim WeekName03 As String Dim WeekName04 As String Dim WeekName05 As String Dim WeekName06 As String Dim WeekName07 As String Dim WeekName08 As String Dim WeekName09 As String Dim WeekName10 As String Dim WeekName11 As String Dim WeekName12 As String Dim WeekName13 As String Dim WeekName14 As String Dim WeekName15 As String Dim WeekName16 As String Dim WeekName17 As String Dim WeekName18 As String Dim WeekName19 As String Dim WeekName20 As String Dim WeekName21 As String Dim WeekName22 As String Dim WeekName23 As String Dim WeekName24 As String Dim WeekName25 As String Dim WeekName26 As String Dim WeekName27 As String Dim WeekName28 As String Dim WeekName29 As String Dim WeekName30 As String Dim WeekName31 As String Dim WeekName32 As String Dim WeekName33 As String Dim WeekName34 As String Dim WeekName35 As String Dim WeekName36 As String ' Dim WeekName37 As String ' Dim WeekName38 As String ' Dim WeekName39 As String ' Dim WeekName40 As String ' Dim WeekName41 As String ' Dim WeekName42 As String ' Dim WeekName43 As String ' Dim WeekName44 As String ' Dim WeekName45 As String ' Dim WeekName46 As String ' Dim WeekName47 As String ' Dim WeekName48 As String ' Dim WeekName49 As String ' Dim WeekName50 As String ' Dim WeekName51 As String ' Dim WeekName52 As String ' Dim WeekName53 As String ' '<<=============================================== ===================== ' These are defined variables to select ranges to autocenter week name! ' '<<=============================================== ===================== Dim Week01 As String Dim Week02 As String Dim Week03 As String Dim Week04 As String Dim Week05 As String Dim Week06 As String Dim Week07 As String Dim Week08 As String Dim Week09 As String Dim Week10 As String Dim Week11 As String Dim Week12 As String Dim Week13 As String Dim Week14 As String Dim Week15 As String Dim Week16 As String Dim Week17 As String Dim Week18 As String Dim Week19 As String Dim Week20 As String Dim Week21 As String Dim Week22 As String Dim Week23 As String Dim Week24 As String Dim Week25 As String Dim Week26 As String Dim Week27 As String Dim Week28 As String Dim Week29 As String Dim Week30 As String Dim Week31 As String Dim Week32 As String Dim Week33 As String Dim Week34 As String Dim Week35 As String Dim Week36 As String ' Dim Week37 As String ' Dim Week38 As String ' Dim Week39 As String ' Dim Week40 As String ' Dim Week41 As String ' Dim Week42 As String ' Dim Week43 As String ' Dim Week44 As String ' Dim Week45 As String ' Dim Week46 As String ' Dim Week47 As String ' Dim Week48 As String ' Dim Week49 As String ' Dim Week50 As String ' Dim Week51 As String ' Dim Week52 As String ' Dim Week53 As String ' '<<=============================================== ===================== ' Assigned variable to worksheet name! '<<=============================================== ===================== Sheetname01 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k3") Sheetname02 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k4") Sheetname03 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k5") Sheetname04 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k6") Sheetname05 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k7") Sheetname06 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k8") Sheetname07 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k9") Sheetname08 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k10") Sheetname09 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k11") Sheetname10 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k12") Sheetname11 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k13") Sheetname12 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k14") Sheetname13 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k15") Sheetname14 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k16") Sheetname15 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k17") Sheetname16 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k18") Sheetname17 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k19") Sheetname18 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k20") Sheetname19 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k21") Sheetname20 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k22") Sheetname21 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k23") Sheetname22 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k24") Sheetname23 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k25") Sheetname24 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k26") Sheetname25 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k27") Sheetname26 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k28") Sheetname27 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k29") Sheetname28 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k30") Sheetname29 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k31") Sheetname30 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k32") Sheetname31 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k33") Sheetname32 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k34") Sheetname33 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k35") Sheetname34 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k36") Sheetname35 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k37") Sheetname36 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k38") ' Sheetname37 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k39") ' Sheetname38 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k40") ' Sheetname39 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k41") ' Sheetname40 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k42") ' Sheetname41 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k43") ' Sheetname42 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k44") ' Sheetname43 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k45") ' Sheetname44 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k46") ' Sheetname45 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k47") ' Sheetname46 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k48") ' Sheetname47 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k49") ' Sheetname48 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k50") ' Sheetname49 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k51") ' Sheetname50 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k52") ' Sheetname51 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k53") ' Sheetname52 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k54") ' Sheetname53 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("k55") '<<=============================================== ================================================== ====== JantoAug = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("a58") SeptoDec = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("a61") WeekAllJantoAug = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("i3") WeekAllSeptoDec = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("i4") '<<=============================================== ================================================== ====== '<<=============================================== ================================================== ====== ' Assigned variable to week name! '<<=============================================== ================================================== ====== WeekName01 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j3") WeekName02 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j4") WeekName03 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j5") WeekName04 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j6") WeekName05 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j7") WeekName06 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("j8") ' ' '<<=============================================== ================================================== ====== ' Assigned variable to week range selection for auto center! '<<=============================================== ================================================== ====== Week01 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l3") Week02 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l4") Week03 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l5") Week04 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l6") Week05 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l7") Week06 = ThisWorkbook.Worksheets("Defined Fiscal Weeks-DND").Range("l8") ' ' '-------------------------------------------------- ' Range(JantoAug).Select Sheets("Daily Entries Jan to Aug").Select Range(WeekAllJantoAug).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.UnMerge Selection.ClearContents Selection.Interior.ColorIndex = 15 ' ' '-------------------------------------------------- Range(SeptoDec).Select Range(WeekAllSeptoDec).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.UnMerge Selection.ClearContents Selection.Interior.ColorIndex = 15 ' ' '-------------------------------------------------- Range(Sheetname01).Select Range(WeekName01).Select ActiveCell.FormulaR1C1 = "Week01" ' Range(Week01).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' '-------------------------------------------------- Range(Sheetname02).Select Range(WeekName02).Select ActiveCell.FormulaR1C1 = "Week02" ' Range(Week02).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' '-------------------------------------------------- Range(Sheetname02).Select Range(WeekName03).Select ActiveCell.FormulaR1C1 = "Week03" ' Range(Week03).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' '-------------------------------------------------- Range(Sheetname04).Select Range(WeekName04).Select ActiveCell.FormulaR1C1 = "Week04" ' Range(Week04).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' '-------------------------------------------------- Range(Sheetname05).Select Range(WeekName05).Select ActiveCell.FormulaR1C1 = "Week05" ' Range(Week05).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' '-------------------------------------------------- Range(Sheetname06).Select Range(WeekName06).Select ActiveCell.FormulaR1C1 = "Week06" ' Range(Week06).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone ' ' ' End Sub |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com