Copy range to new WB

[alexmb1]

I am working on a form in excel with several macros. Currently, I have it set up so the user clicks a "save" button and the sheet they are working on saves to a specified file path under a new name as a new xls. The problem is that the macro buttons that I designed also transfer to the new wb since they are part of the sheet. I have tried and tried to just copy the range that contains the form but keep getting errors when I run the macro.

currently my code is this:

Sub Save_Macro()
Dim wb As Workbook
Dim NewShtName As String
Dim FilePath As String

FilePath = Sheets("inputs").Range("b36").Value
NewShtName = Range("E3")

ActiveSheet.Unprotect

Sheets("Form").Copy
Set wb = ActiveWorkbook
wb.Sheets("Form").Name = NewShtName

ActiveWorkbook.SaveAs (FilePath & NewShtName & "xls")
ActiveWorkbook.Close
ActiveSheet.Protect
End Sub

simple, i know. the range I need to copy from this sheet ("Form") is D2:V29.

Any ideas on how i can make this work?

also, I have tried sheets("Form").Range("D2:V29").Copy and it did not work for me.

 

[Kiwiman]

Howzit

Give this a go. You will need to change where the filePath and Filename are coming from as I have modified this so it works on mine. I have also not taken into account anything to do with protecting \ unprotecting sheets. Please reinstate them.

Sub CopyRange()

Dim wb As String
Dim NewWbk As String
Dim NewShtName As String
Dim FilePath As String

    ' Assign active workbook to variable wb
    wb = ActiveWorkbook.Name
    
    ' Get the File Path location
    FilePath = Sheets("inputs").Range("b1").Value
    
    ' Get the new File name
    NewShtName = Sheets("inputs").Range("b3").Value

    ' Add a new workbook
    Application.Workbooks.Add
    Range("A1").Select

    ' Save the new workbook and specify location and name
    ActiveWorkbook.SaveAs Filename:=FilePath & NewShtName & ".xls"
    
    ' Assign the active workbook to the NewWbk variable
    NewWbk = ActiveWorkbook.Name

    ' Activate the initial window that the macro was run from, and activate the Form sheet
    Workbooks(wb).Sheets("Form").Activate
   
    ' Unprotect current sheet
    ' ActiveSheet.Unprotect

    'Select the range to copy
    Range("D2:V29").Select

    ' Copy the selected range
    Selection.Copy
    
    ' Activate the new workbbook selecting sheet1
    Workbooks(NewWbk).Sheets("Sheet1").Activate

    ' Paste it into the new workbook
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    ' Rename Sheet1
    ActiveSheet.Name = NewShtName
        
    'Save and close New Workbook
    ActiveWorkbook.Save
    ActiveWorkbook.Close   

    ' Protect current sheet
    ' ActiveSheet.Protect 

End Sub