VBA help - Copy sheet into new worksheet if criteria is met

[TMcKenzie]

Hi,

I am trying to create a macro that copies a specific sheet into a new workbook depending on a dropdown list in cell O2 containing specific criteria.

I have 6 worksheets called 'SD Upload 1', 'SD Upload 2', 'SD Upload 3', 'SD Upload 4', 'SD Upload 5', 'SD Upload 6' & cell O2 contains a dropdown list of Phase 1-6 so would like these to marry up with the 'upload' tabs.

Any help would be appreciated.

Thanks,

Taylor

 

[tonyyy]

Taylor,

The following might get you started...

Sub Sheet_SaveAs()
Dim str As String
Dim wb As Workbook

Application.ScreenUpdating = False
str = "SD Upload " & Right(Sheets("Sheet1").Range("O2"), 1) '<----Change Sheet1 to the name of the sheet with the dropdown list
Sheets(str).Copy
Set wb = ActiveWorkbook
With wb
    .SaveAs ThisWorkbook.Path & "\" & str & " " & Format(Date, "yyyymmdd")
    .Close False
End With
Application.ScreenUpdating = True
End Sub

Cheers,

Tony