Sort spreadsheets that meet a certain condition

**samnic** · 02-14-2010, 04:40 AM

Hi all,
I want to write a macro which will look at a cell in a spreadsheet (e.g.[A9]) then, based on the value in that cell, save a copy of the spreadsheet into one of two different locations.

That is, I want to have a macro similar to the IF function:
IF(A9=2,save a copy to folder 1, save a copy to folder 2)

Is this possible.
Any help greatly appreciated

**DonkeyOte** · 02-14-2010, 05:05 AM

Assuming you want to continue working in the active workbook rather than the "to be" saved version you could perhaps use SaveAsCopy method ?

Sub Example()
Dim strPath As String
Select Case UCase(Sheets("Sheet1").Range("A9").Value)
    Case "A"
        strPath = "C:\xyz\abc\"
    Case "B"
        strPath = "C:\aaa\zys\"
End Select
If strPath <> "" Then ActiveWorkbook.SaveCopyAs strPath & ThisWorkbook.Name
End Sub

If you want to change the current file to the "to be" Saved version then use SaveAs rather than SaveCopyAs.

**samnic** · 02-14-2010, 05:20 AM

Thanks for that.
So where exactly do I specify the criteria which tells it how to choose between case A and case B?
I still don't see how its going to make a decision based on what cell A9 says

**DonkeyOte** · 02-14-2010, 05:45 AM

In short the sample code tests against the value of Sheet1!A9 ... if the value is "A" do x if "B" do y else do nothing.

For a brief overview of Select Case see: http://www.ozgrid.com/VBA/select-case.htm