-7

I have 2 workbooks for the department. "Workorders" & "Completed Workorders" "Workorders" is a shared book that other departments put in Task request. The guys in the department, fill them out when they have completed the task. Each Sheet is renamed with the task number ID, so the sheet is never the same name. The "Workorders" book needs to be the main book. So is there a way for them to have a command button "Complete", that would move the active sheet they have filled out in "Workorders" into the "Completed Workorders" book. Either keep it open in the background, so they never see it, or if it can open, move the sheet, and close back.

thank you for your help, and i do hope it is possible.

VBA Pete
  • 2,656
  • 2
  • 24
  • 39
wpeterson
  • 21
  • 2
  • 1
    I think this post may help you. [See this link](http://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba) – RyanL Jan 31 '17 at 20:51

1 Answers1

0

Insert a button on the workbook and assign this code to the button, make sure to change the filename path to yours:

Private Sub CommandButton1_Click()
    On Error Resume Next
    Dim sheetIndex As Integer
    sheetIndex = 1
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="C:\YourPath\Completed Workorders.xlsm"
    Windows("Workorders.xlsm").Activate
    ActiveSheet.Select
    ActiveSheet.Copy Before:=Workbooks("Completed Workorders.xlsm").Sheets(sheetIndex)
    sheetIndex = sheetIndex + 1

    ActiveWorkbook.Save
    Windows("Completed Workorders.xlsm").Close
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
pokemon_Man
  • 902
  • 1
  • 9
  • 25
  • Thank you, so much for helping me with this. I did not explain very well that each sheet that has to moved is a different number (example - Pr1807 or MA17005). Can you use this with out having to put the exact name of the sheet? thank you again – wpeterson Jan 31 '17 at 21:22
  • check my edited post. – pokemon_Man Jan 31 '17 at 21:29
  • It works, so I am not sure what i have done. After it ask to save "Completed Workorders" which I say yes too. I get an error. Run-Time Error '9': Subscript out of range. when i hit debug the following line is highlighted -currentSheet.Copy Before:=Workbooks("Completed Workorders.xlsx").Sheets(sheetIndex) – wpeterson Jan 31 '17 at 21:48
  • umm, it shouldn't ask to save completed workorders because it already saves it before closing it. try putting this at the begining of the code Application.DisplayAlerts = False – pokemon_Man Jan 31 '17 at 21:53
  • check if your workbook is named "Completed Workorders.xlsx" – pokemon_Man Jan 31 '17 at 21:56
  • It is named "Completed Workorders.xlsm". I changed the .xlsx to the .xlsm. Is this what i did wrong? – wpeterson Jan 31 '17 at 22:07
  • yes, if you change the extension, you must change it in the code as well - I've modified my answer to match your file extension. – pokemon_Man Jan 31 '17 at 22:09
  • It is working except it is moving all the sheets in "Workorders" to "Completed workorders" not just the one sheet that i am working on. – wpeterson Jan 31 '17 at 22:21
  • ok, take a look at edited post answer, it will only take the current sheet that has the button on and copy over. – pokemon_Man Jan 31 '17 at 22:26
  • It works, perfect!!! thank you so much for being so patient with me and helping, it is truly appreciated. your my knight in shining amour!! – wpeterson Jan 31 '17 at 22:34
  • Please mark this post as the answer if it help you. thanks. – pokemon_Man Jan 31 '17 at 22:34
  • Hello, I hate to ask you another question, but is there any way we can move the page completely, with out coping it? – wpeterson Feb 01 '17 at 14:08
  • you mean, after copy, delete the worksheet that you are working on in "Workorders.xlsm" ? – pokemon_Man Feb 01 '17 at 14:31
  • Yes, after it is moved to "Completed Workorders.xlsm" it has to be removed from "Workorders.xlsm". I was going to see if it could be added to this code. - thank you – wpeterson Feb 01 '17 at 14:48
  • Try updated answer. – pokemon_Man Feb 01 '17 at 14:58
  • Works like a dream, thank you for the addition. It is greatly appreciated. – wpeterson Feb 01 '17 at 15:24