Copy and Move Worksheet

[dgaller]

I am looking for a macro were I can hit Ctrl-r and have the activesheet move to a new workbook saving it with the name in cell B1. I have tried many different codes I have found but they all seem more complicated than I need. Can someone help?

 

[jbeaucaire]

Could be this simple:

Option Explicit

Sub MoveSave()
Dim fName As Range, fPath As String

    ActiveSheet.Move
    fPath = "C:\My Files\"      'path to save into, rememeber the final \
    fName = Range("B1").Value   'filename to use
    If fName = "" Then fName = "Filename missing " & Format(Now, "#.0")
    
    ActiveWorkbook.SaveAs fPath & fName, xlNormal

End Sub

It is possible that the tesxt value in B1 has illegal filename characters, that would cause errors.

 

[Peter_SSs]

Or perhaps just putting it back into the same folder that the original workbook is in? I haven't done any error-checking on B1. Apart from blank, there could be other illegal file names in there so I have assumed B1 contains a legal file name.

One other slight difference with my code to Jerry's is that mine leaves the worksheet in the original workbook as well as copying it to a new workbook whereas Jerry's removes the worksheet from the original workbook. If the original workbook only has one sheet then you cannot use .Move (but you could just SaveAs with a new filename).

So choose .Move or .Copy as best fits what you want.

Sub MoveSave2()
    Dim s1 As String, s2 As String
    
    s1 = ActiveWorkbook.Path & Application.PathSeparator
    s2 = Range("B1").Value
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs s1 & s2
End Sub

 

[dgaller]

Thank you this is what I was looking for. However I am getting a runtime error for Object variable or with block variable not set.

Highlighing

fName = Range("B1").Value

 

[Peter_SSs]

Try changing

Dim fName As Range, fPath As String

to

Dim fName As String, fPath As String

Did you try my code or are you definitely moving the sheet to a different folder?

Can you confirm whether you want to move or copy the worksheet to a new workbook?

Can you confirm whether your original workbook will always have more than one sheet to start with?