How do you make a VB Button in a workbook that will copy a excel sheet, rename it with the current weeks start date (ie 22 March 2004 ) and store it in a differant folder or workbook?

Perhaps as a starting point, you could record the macro, then see what areas need refinement. Recording macros is one of the best ways to learn VBA. If you are stuck, then you can post back with what else needs fixing.

After getting the macro set up, then it is easy to attach it to a button. Draw the object, right-click and choose "assign macro" and you are done, except for the adjusting the appearance of the button.

Right thanks for that.

Ive got it working in a macro but when I copy the code to VB there is a problem with range select.

Code is as follows



Private Sub CommandButton6_Click()

Sheets("Duty Roster").Select
Range("B4:K241").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Duty Roster Basic").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Duty Roster").Select
Range("H3:K3").Select

Application.CutCopyMode = False
Selection.Copy
Sheets("Duty Roster Basic").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Duty Roster Basic").Select
Sheets("Duty Roster Basic").Copy

ActiveWorkbook.Close
Windows("Duty And Signing On Sheets 2.xls").Activate
Sheets("Home").Select

End Sub

If you try this this should work.

all ive done to your code is stopped it selecting various ranges but just copying and pasting them

also you were wanting to obtain the Current Weeks start date
you could use this to get mondays date

Dim MyDate As Date, strMyDate As String
'Current Weeks Start Date
MyDate = Date - Weekday(Date, vbMonday) + 1 'myDate is Mondays Date
strMyDate = CStr(Format(MyDate, "dd mmmm yyyy"))
Range("A1").Formula = strMyDate

Hope this Helps

David

Thanks it works a treat.

Mark