macros vba question

  • Thread starter Thread starter dlotz
  • Start date Start date
D

dlotz

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/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch
 
Back
Top