Results 1 to 5 of 5

Thread: Copy to archive

  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Unanswered: Copy to archive

    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?

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.

  3. #3
    Join Date
    Mar 2004
    Posts
    13
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    If you try this this should work.

    Code:
    Worksheets("Duty Roster").Range("B4:K241").Copy
    Worksheets("Duty Roster Basic").Range("B4").PasteSpecial Paste:=xlPasteValues
    
    Worksheets("Duty Roster").Range("H3:K3").Copy
    Worksheets("Duty Roster Basic").Range("B3").PasteSpecial Paste:=xlPasteValues
    
    Worksheets("Duty Roster Basic").Activate
    
    ActiveWorkbook.Close
    Windows("Duty And Signing On Sheets 2.xls").Activate
    Worksheets("Home").Activate
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    Thanks it works a treat.

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •