Solved: Complete excel sheet on command

[jimgus]

I currently have the following code associated with a command button on the sheet for an employee to lock a worksheet once they are complete. I also need a way that when this button is activated to copy and paste the worksheet range to another workbook on a specific worksheet and range, and then close and save the active worksheet in the orginal workbook. Here is the code I am using for the lock portion of this request now. Hope I have explained it well enough.

Sub LockAll()
ActiveSheet.Unprotect Password:="password"
Cells.Locked = True
ActiveSheet.Protect Password:="password"

End Sub

[Simon Lloyd]

This is a bit of clumsy code but then again it is very very late!

Sub Lock_n_Paste()
Dim wbMe As Workbook, wbOpen As Workbook
Dim strSheet As String
Dim rng As String
ActiveSheet.Unprotect Password:="password"
rng = ActiveSheet.UsedRange.Address
strSheet = ActiveSheet.Name 'sheet your working on
Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open _
(Filename:="C:\Documents and Settings\USER\My Documents\test.xls", Editable:=True)
wbMe.Sheets(strSheet).Range(rng).Copy _
Destination:=wbOpen.Sheets("Sheet2").Range("A6")
ActiveWorkbook.Save 'save newly opened workbook
ActiveWorkbook.Close 'close newly opened workbook
ActiveSheet.Protect Password:="password"
ThisWorkbook.Save 'save original workbook
Application.Quit 'close excel
End Sub
change USER for your username, or indeed change the path to suit you.

[jimgus]

Thanks. I am new at VBA and this has been a great help. The help from everyone is appreciated. jrg

[Simon Lloyd]

Just be aware that that particular code uses "UsedRange" that means it copies eveything on the sheet, you can change that to be any range you like!

[jimgus]

I noticed one problem using the command button on the spreadsheet. It continues to work on subsquent openings of the sheet and tries to repaste to the second workbook any time it is activated. Is there a way to disable the button or macro once it has done its job and that it could only be reset or restarted by appropriate personnel. I thought the protection would take care of it but it didn't. Thanks for your help. Starting to get the hang of this if only at a very basic level. jrg