VBA - Move worksheet to new workbook and save to the same file location

[Jon_H]

Hi everyone;

I'm very new to VBA so please excuse what is probably a very basic question, and my less than perfect coding!! I've written a VBA which at the end I'd like it to move the active worksheet to a new workbook and save that new workbook to the same file path as the original workbook saving it as the worksheet name. Everything is fine up until the last four lines of the code - would appreciate some help!

Here's my code -

Sub Test_()

'
' Create_77_sheets_for_X Macro
'


    Sheets("X Single Template").Select
    Sheets("X Single Template").Copy After:=Sheets(4)
    Sheets("Main Data").Select
    Range("A5").Offset(1, 0).Select
    Selection.Copy
    Sheets("X Single Template (2)").Select
    Range("J30").Select
    ActiveSheet.Paste
    ActiveSheet.Name = ActiveSheet.Range("J30").Value
    ActiveSheet.Select
    Cells.Select
    Range("H1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Move
        Dim thisWb As Workbook
    
    Set thisWb = ActiveWorkbook
    ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\new workbook.xls"
    ActiveWorkbook.Close savechanges:=False
    
End Sub

 

[Jon_H]

Can anyone help please?

 

[Norie]

Try this.

ActiveSheet.Move

With ActiveWorkbook
    .SaveAs Filename:=ThisWorkbook.Path & .Sheets(1).Name & ".xls"
    .Close savechanges:=False
End With

 

[Jon_H]

Thanks for your time Norie

It certainly got further than mine before; but it didn't save it to the file folder of where the original Workbook is saved. When I re-ran it to find out where it was being saved it came up with the following (screen shot linked below) -

 

[Norie]

Can't see the screenshot I think there might be a missing \ in the code I posted.

ActiveSheet.Move

With ActiveWorkbook
    .SaveAs Filename:=ThisWorkbook.Path & "\" &  .Sheets(1).Name & ".xls"
    .Close savechanges:=False
End With

 

[Jon_H]

It still did the same but I've changed your code and have now added in the file location as it will always be the same and it worked just fine.

Thanks very much for your help.