VBA in shared workbook

  • Thread starter Thread starter Very Basic User
  • Start date Start date
V

Very Basic User

Hello,
I have a file that consists of a blank form or template that is filled out
during the week. At the beginning of the following week, the user currently
hits a command button that copies and pastes the work in progress sheet as a
new sheet named as a date, and then clears the original form to start new.
Here is the current code...

Sub Save_sheet()

MondayDate = Range("I4").Value

Sheets("Entry Sheet").Select
Sheets("Entry Sheet").Copy After:=Sheets(1)
Sheets("Entry Sheet (2)").Select
Sheets("Entry Sheet (2)").Name = Format(MondayDate, "yyyy-mm-dd")
Sheets("Entry Sheet").Select


Range("E17:O22").Value = ""
Range("E36:O41").Value = ""
Range("E55:O60").Value = ""
Range("E74:O79").Value = ""
Range("E93:O98").Value = ""
Range("E112:O117").Value = ""
Range("E131:O136").Value = ""
Range("E150:O155").Value = ""
Range("E169:O174").Value = ""
Range("E188:O193").Value = ""

Range("A1").Select
ActiveWorkbook.Save

Problem is sharing the file as multiple people will need to fill in the
form. When I share and try to run the code I get "run-time error 1004
unavailable in shared workbook"

Any help would be appreciated!
 
Apparently, you can't copy a worksheet in a shared workbook. however, you can
insert a new worksheet and then copy all of one sheet into another sheet.
Depending on your setup, this might work for you?
 
Thank you Luke for the follow up! My VBA skills are nil, I generally just
copy / paste and peck to change to fit my application. Would you be able to
show me the code that would do that? My file name is currently : SDU
Tracking.xlsm, the sheet or tab that is being filled in is called: Entry Sheet

I would really appreciate the time!
 
I believe the following will work for you

Sub Save_Sheet()
MondayDate = Range("I4").Value
Sheets.Add After:=Sheets(1)

ActiveSheet.Name = Format(MondayDate, "yyyy-mm-dd")
x = ActiveSheet.Name
Sheets("Entry Sheet").Cells.Copy
Sheets(x).Range("A1").Select
ActiveSheet.Paste

Sheets("Entry Sheet").Select


Range("E17:O22").Value = ""
Range("E36:O41").Value = ""
Range("E55:O60").Value = ""
Range("E74:O79").Value = ""
Range("E93:O98").Value = ""
Range("E112:O117").Value = ""
Range("E131:O136").Value = ""
Range("E150:O155").Value = ""
Range("E169:O174").Value = ""
Range("E188:O193").Value = ""

Range("A1").Select
ActiveWorkbook.Save

End Sub
 
Luke, one other question, in the code, it currently pastes the information
from one sheet to the next which is what I asked for, is there a way to paste
special value only. What happens is the cells with formulas in it on the main
sheet are not pasting correctly onto the new sheet being created.

Thanks,
John
 
Back
Top