A macro like this is just what I am after.
I'll try and explain again what I'm after.
each sheet has a row that contains columns for each day in the week.
The cell range of this row with dates is B2:F2 in each sheet, as
below:
A B C D E F
1
2 Date 19/09/11 20/09/11 21/09/11 22/09/11 23/09/11
Each sheet should have the name of the week i.e. 19 - 23 Sep, Don't
care too much what the title is as long as it is easy to read the
dates.
I have a sheet that contains a template of the info I need, so at
present I copy the sheet, rename it, then alter the formula in cell B2
to be
='Name of previous sheet'!F2+3
the formula's in C2, D2, E2 and F2 are
= B2+1, C2+1, D2+1, E2+1 respectively.
Thus my dates are calculated.
What I would like is to say run a macro that inserts a new sheet with
the template of info and then does the rename of the sheet and the
alteration of the formula in B2 so that the dates are calculated
correctly.
Thanks again.
Ron Rosenfeld wrote:
> On Tue, 13 Sep 2011 08:05:03 -0700 (PDT), Keith Patrick-Ward <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >I have an excel document which it is intended to have a sheet for each
> >work week. In each sheet I have cells that have the dates for Monday
> >through to Friday.
> >Everytime I create a new sheet (from a template sheet using the copy
> >sheet method) I have to name it manually and then change the date of
> >the Monday cell so that Tuesday to Friday can be calculated.
> >
> >What I would like is for the the date columns for Monday to Friday to
> >be automatically calculated, and if possible the name of the sheet
> >too.
> >
> >
> >e.g. My sheet names are
> >
> >12th - 16th Sept | 5th - 9th Sept | 29th Oct - 2nd Sept
> >
> >these sheets have cells B2 - F2 which contain the dates and are
> >calculated as thus, B2 -- ='5th - 9th Sept'!F2+3 C2 -- = B2+1 etc
> >etc
> >
> >So what suggestions to make this work so I can just slot a new sheet
> >in?
> >
> >Thanks for any help
>
> I'm not sure from your description what it is that is in B2:F2, other than that they are dates.
>
> But the following macro should give you some ideas as to how to proceed. It generates and names a new worksheet, based on the contents of B2 in your existing sheets, and then fills in B2:F2 with the appropriate dates.
>
> As written, it uses standard THREE(3) letter abbreviations for dates. I note in your examples that you have mixed both three and four letter abbreviations. If that's what you really want, you will need to supply the individual abbreviations for each month.
>
> To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
> Ensure your project is highlighted in the Project Explorer window.
> Then, from the top menu, select Insert/Module and
> paste the code below into the window that opens.
>
> To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
>
> With more information, it would be easy to copy or program the complete template onto the new sheet.
>
> ======================================
> Option Explicit
> Sub AddNewWeekdaySheet()
> Dim ws As Worksheet
> Dim dDate As Date
> Dim dLast As Date
> Dim sLastWS As String
> Const FirstDate As String = "B2"
> Dim NewSheetName As String
>
> 'Determine Last Date
> For Each ws In Worksheets
> If IsDate(ws.Range(FirstDate)) Then
> dDate = ws.Range(FirstDate).Value
> dLast = IIf(dDate > dLast, dDate, dLast)
> sLastWS = IIf(dDate >= dLast, ws.Name, sLastWS)
> End If
> Next ws
>
> NewSheetName = OrdinalNum(Day(dLast + 7)) & Format(dLast + 7, " mmm") &_
> " - " & OrdinalNum(Day(dLast + 11)) & Format(dLast + 11, " mmm")
>
> Worksheets.Add after:=Worksheets(sLastWS)
> ActiveSheet.Name = NewSheetName
> With Worksheets(NewSheetName)
> .Range(FirstDate).Value = dLast + 7
> With .Range(.Range(FirstDate), .Range(FirstDate)(0, 5))
> .DataSeries Type:=xlChronological, Date:=xlDay
> .NumberFormat = "d mmm"
> End With
> End With
> End Sub
> '--------------------------------------------------
> Private Function OrdinalNum(num) As String
> Dim Suffix As String
>
> OrdinalNum = num
> If Not IsNumeric(num) Then Exit Function
> If num <> Int(num) Then Exit Function
>
> Select Case num Mod 10
> Case Is = 1
> Suffix = "st"
> Case Is = 2
> Suffix = "nd"
> Case Is = 3
> Suffix = "rd"
> Case Else
> Suffix = "th"
> End Select
>
> Select Case num Mod 100
> Case 11 To 19
> Suffix = "th"
> End Select
>
> OrdinalNum = Format(num, "#,##0") & Suffix
> End Function
> ================================
|
Similar Threads
