Move worksheet

[Aphten]

Hello there,

I am trying to move one worksheet to another workbook via macro assigned to a button. I have a worksheet named "Med A Audit (current test)" that I use as a blank form. Once I have completed the form, I would like to move it to another workbook where all the other audits that have been completed for the month are stored. I have tried the following code:

Sub Transfer_Med_A_Audit()
'transfers completed Med A Audit then closes it out without saving.
    Workbooks.Open Filename:="C:\Users\Paige\Documents\Work\2 Med A Audits\February 2025 Med A Audits.xlsm"
    Sheets("Sheet17").Move after:=Workbooks("February 2025 Med A Audits.xlsm").Sheets("Sheet1")
    
Application.DisplayAlerts = False
    Workbooks("Med A Audit (current test)").Close
Application.DisplayAlerts = True
End Sub

I wanted this macro to open the workbook that stored the months audits, transfer the completed audit, and then close out the audit without saving, so it remains a blank form. The macro successfully opens the other workbook but then gives me a "subscript out of range" notification. Everything but moving the worksheet works fine. I swear it worked at one point, but now it is not.

Originally, I wanted to be able to keep a blank form and then copy that for all new audits and keep everything (blank form and completed audits) in the same workbook, but I guess since I am copying the macro-enabled worksheet, it slows down excel really bad. This was my work around. If you have a solution to be able to do this, I would love to see it. The workbook that stores all the completed audits has all the worksheets hidden. If I need to refer back to one, I unhide only that one. It still slows down excel, but not quite to the point of seemingly becoming frozen, if I only have one worksheet unhidden at a time.

 

[Logit]

The "Subscript out of range" error in this macro likely occurs due to one or more of the following reasons:

1.​

• The macro assumes that "Sheet17" exists in the currently open workbook. If it doesn't, VBA will throw a "Subscript out of range" error.
• Double-check the sheet names in the workbook you're opening to ensure "Sheet17" exists and is spelled correctly (watch out for extra spaces!).

2.​

• In this line:
  
  vba
  Sheets("Sheet17").Move after:=Workbooks("February 2025 Med A Audits.xlsm").Sheets("Sheet1")<br>
  The workbook named "February 2025 Med A Audits.xlsm" must already be open for the code to work. If it's not open, VBA cannot find it, which results in a "Subscript out of range" error.
• Also, ensure the workbook name matches exactly, including the file extension (.xlsm in this case).

3.​

• The macro tries to move "Sheet17" after Sheets("Sheet1") in the workbook "February 2025 Med A Audits.xlsm". If "Sheet1" is not present in the workbook, you'll get the error.

4.​

• If the Filename in the Workbooks.Open statement is incorrect (e.g., the file path or name is wrong), the workbook won't open, and any attempt to reference it later will fail. Ensure the file path and name are correct.

You could add checks to your code to catch errors if they exist and prevent the the macro from crashing :

Sub Transfer_Med_A_Audit()
    On Error Resume Next ' To handle potential errors
    ' Open the workbook
    Workbooks.Open Filename:="C:\Users\Paige\Documents\Work\2 Med A Audits\February 2025 Med A Audits.xlsm"
    If Err.Number <> 0 Then
        MsgBox "Failed to open workbook. Check file path.", vbCritical
        Exit Sub
    End If
    On Error GoTo 0 ' Reset error handling
    
    ' Check if Sheet17 exists before attempting to move it
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Sheet17")
    If ws Is Nothing Then
        MsgBox "Sheet17 not found.", vbCritical
        Exit Sub
    End If
    On Error GoTo 0
    
    ' Move the sheet if everything is correct
    ws.Move after:=Workbooks("February 2025 Med A Audits.xlsm").Sheets("Sheet1")
    
    ' Close the test workbook
    Application.DisplayAlerts = False
    On Error Resume Next
    Workbooks("Med A Audit (current test)").Close
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

 

[Aphten]

Thank you! I don't know why I was having so much trouble with this but this seems to have solved my problem. Any ideas for my other issue: not being able to just copy my form in the same worksheet without bogging down Excel?

 

[Logit]

The "moving the worksheet" macro appears to be clean. It is most likely something in your "copying process" that is bogging things down.

 

[Aphten]

The "moving the worksheet" macro appears to be clean. It is most likely something in your "copying process" that is bogging things down.

I read online that if you copy a macro-enabled worksheet within the same workbook, it causes problems. Is there a way to copy it without this happening? Essentially, I have a blank form that has lots of VBA coding and various macros that I need to repeatedly use. I could save it as a template, but I would also like to keep completed forms in the same workbook for each month.