is there a way that when I save as a file.

If a cell in a worksheet says Week 1 it will copy and paste with vaules a col
if if says week 2 is will copy and paste with values a different col in the
worksheet

You can do this with a macro. I am assuming you mean a particular cell on a
particular sheet, not just any cell on any sheet. The following code checks
cell A1 on Sheet1 whenever you save the workbook. If A1 contains Week 1 then
column D is copied & pasted in place as values. If A1 contains Week 2 then
column E is copied & pasted in place as values.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim txt As String
On Error Resume Next
txt = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
Select Case txt
Case "Week 1"
Columns("D:D").Select
Case "Week 2"
Columns("E:E").Select
Case Else
Exit Sub
End Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

This code must be placed in the the ThisWorkbook module of your workbook.
Right-click on any sheet tab and select View Code. This opens the Visual
Basic Editor (VBE). Select View Project Explorer. You'll see a
'VBAProject' for your workbook with the sheets listed under it and something
called ThisWorkbook. Double-click on ThisWorkbook. Paste the above code in
the big empty window. Edit the sheet name, cell reference, and columns as
needed. Then select File Close to return to normal Excel.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

" wrote:

is there a way that when I save as a file.

If a cell in a worksheet says Week 1 it will copy and paste with vaules a col
if if says week 2 is will copy and paste with values a different col in the
worksheet