VBA to save sheets in new file

[Karimae92]

I'm trying to save an individual worksheet in a certain location and renaming the new worksheet to a specific cell.

The workbook consists of 10 tabs of which I only require the tab 'User Form' to be saved as a separate workbook and renamed to the text in cell B4. I've managed to save the workbook, it's just the renaming side of things I'm having difficulties with.

help!

Sub UserForm()
    ActiveSheet.Copy
    With ActiveWorkbook
        .SaveAs "C:\Documents\Projects\New Starters" & .Sheets(1).Name
        .Close 0
    End With
End Sub

 

[steve the fish]

Try this:

Sheets("User Form").Copy

With ActiveWorkbook
    If .Range("B4").Value <> "" Then
        .SaveAs "C:\Documents\Projects\New Starters\" & .Range("B4").Value & ".xlsx"
        .Close False
    End If
End With

 

[Karimae92]

Hi There,

I received the below:

Run-time Error '483':

Object doesn't support this property or method.

When clicking on the "Debug", it highlights If .Range("B4").Value <> "" Then

The cell is simple text.

Thanks

 

[steve the fish]

Sorry that should be With ActiveSheet on the line above.

 

[steve the fish]

Hold on. Its this:

Sheets("User Form").Copy

With ActiveWorkbook
    If Range("B4").Value <> "" Then
        .SaveAs "C:\Documents\Projects\New Starters\" & Range("B4").Value & ".xlsx"
        .Close False
    End If
End With

 

[Karimae92]

Steve you are amazing!

Works perfectly!

Thank you for your help!