VBA help

[ajc623]

I have a macro that print out a daily schedule just as I would like but now I am trying to add a couple more functions to it

1.After I print the file for the day I want to copy the sheet named "Today" and rename it as the next work day (M-F) date formatted as mmddyy, so if today is January 3, 2014 I would want to name the new sheet 010613 or if it was 1/6/2014 I would want it named 010714. What I have now works but names the file with the current date-
Sheets("Today").Copy Before:=Sheets(9)
Sheets("Today (2)").Select
Sheets("Today (2)").Name = Format(Date, mmddyy)

The second thing I would like to do is automatically do is have a range of cells (D20:K21) be filled with the text "Meeting" every Friday without me having to type it in. I know how to

I am not sure I have explained this well but can give you more info or even send a copy of the file I am working with so you can see what I am trying to do. Thanks

Andrew
andrewc@vatainc.com

 

[JLGWhiz]

For the sheet name, change this:

Sheets("Today (2)").Name = Format(Date, mmddyy)

To this:

Sheets("Today (2)").Name = Format(Date + 1, mmddyy)

I don't know where you would want to insert the following into your code, but the syntax for filling the range would be:

If Weekday(Date) = vbFriday Then
    Range("D20:K21") = "Meeting"
End If

 

[ajc623]

Copied the wrong thing, should be
Sheets("Today").Copy Before:=Sheets(9)
Sheets("Today (2)").Select
Sheets("Today (2)").Name = Format(Date, "mmddyy")

 

[ajc623]

That works when the next day is a work day but is there a way to have it name the sheet the next work day? For example today I will fill in data for Monday and print it out, I then want to run the macro and have it name the sheet 010614 as that is the date it relates to. With-

Sheets("Today (2)").Name = Format(Date + 1, "mmddyy")

formula it will take todays sheet and name it 010414 and I would want it to be named 010614 and then on Monday with the date being 1/6/14I would want the copied and renamed sheet to be 010714. THanks

 

[JLGWhiz]

That works when the next day is a work day but is there a way to have it name the sheet the next work day? For example today I will fill in data for Monday and print it out, I then want to run the macro and have it name the sheet 010614 as that is the date it relates to. With-

Sheets("Today (2)").Name = Format(Date + 1, "mmddyy")

formula it will take todays sheet and name it 010414 and I would want it to be named 010614 and then on Monday with the date being 1/6/14I would want the copied and renamed sheet to be 010714. THanks

If Weekday(Date) = vbFriday Then
 Sheets("Today (2)").Name = Format(Date + 3, "mmddyy")
Else
 Sheets("Today (2)").Name = Format(Date + 1, "mmddyy")
End If

 

[ajc623]

Thanks that works great! On the filling of the range I think I am missing something, below is what I am trying to use

Sub Rename()
'
' Rename Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Sheets("Today").Copy Before:=Sheets(9)
Sheets("Today (2)").Select
If Weekday(Date) = vbFriday Then
Sheets("Today (2)").Name = Format(Date + 3, "mmddyy")
Else
Sheets("Today (2)").Name = Format(Date + 1, "mmddyy")
End If
If Weekday(Date) = vbFriday Then
Range("D20:K21") = "Meeting"
End If
End If
Sheets("Blank").Select
Range("D2:K40").Select
Range("K40").Activate
Selection.Copy
Sheets("Today").Select
Range("D2").Select
ActiveSheet.Paste
Range("D5").Select
End Sub

I would again like for this to happen if the next work day is Friday. So it would populate it based on the next work day. THanks in advance for the help

 

[JLGWhiz]

You have one too many "End If" in it for one thing.
Define "This"

I would again like for this to happen if the next work day is Friday.