title: How to copy a weekly worksheet with a specific column of data onto a new worksheet
body: <p>Hopefully someone knows the answer...I am new to usng VBA on excel.</p>
  
  <p>I am trying to create a Weekly Grocery budget for our Canteen Staff.
  What i have been trying to do is to allow the user to create each weekly budget on a new worksheet.</p>
  
  <p>What i have created so far is Table that includes a "Stock Start" and a "Stock End" column. What the Head of the Kitchen would like is for the "Stock End" column to move over to the "Stock Start" column when a new worksheet is created.</p>
  
  <p>I thought the best way would be to allow the user to populate the worksheet with data and then on the click of a button it would create a new worksheet and copy the "stock end" column into the "stock Start" of the newly created worksheet.</p>
  
  <p>I also wanted to set up a standardised naming convention, such a a "week beginning DD/MM/YY. But this proved too diffcult. </p>
  
  <p>I have managed to find 2 separate pieces of code.</p>
  
  <p>The first part duplicates the "Master" worksheet and allows the user to name the new worksheet themselves. This would be perfect, but it only relies on the "Master" worksheet. What I would prefer is for it to duplicate the Active worksheet.</p>
  
  <p>The second part of the code which I have blocked of with '. will copy the data from Stock End (G4:G83) to the new sheet Stock Start (C4:C83). </p>
  
  <p>But this code needs to know the names of the worksheets in which it is copying...</p>
  
  <p>Becuase of this I can't get the 2 codes to work as one. I am at a lost...</p>
  
  <p>Here is a copy of the spreadsheet in question on my google drive:</p>
  
  <p><a href="https://drive.google.com/open?id=0B7Ahe17Osa3OQjlYY3FxcHd1YVE&amp;authuser=0" rel="nofollow">Weekly Gorcery Budget</a></p>
  
  <p>Kind Regards</p>
  
  <p>T</p>
  
  <pre><code>Private Sub CommandButton1_Click()
      Dim sName As String
      Dim wks As Worksheet
      Worksheets("Master").Copy after:=Sheets(Worksheets.Count)
      Set wks = ActiveSheet
      Do While sName &lt;&gt; wks.Name
          sName = Application.InputBox _
            (Prompt:="Enter new worksheet name")
          On Error Resume Next
          wks.Name = sName
          On Error GoTo 0
      Loop
      Set wks = Nothing
  
      'Worksheets(1).Range("G4:G83").Copy
      'Worksheets(2).Range("C4:C83").PasteSpecial xlPasteValues
  
  End Sub
  </code></pre>