|
VBA in shared workbook
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! -- Thank you for your time! John |
VBA in shared workbook
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? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John |
VBA in shared workbook
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! -- Thank you for your time! John "Luke M" wrote: 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? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John |
VBA in shared workbook
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John "Luke M" wrote: 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? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John |
VBA in shared workbook
Amazing, works like a charm, thank you very much!
-- Thank you for your time! John "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John "Luke M" wrote: 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? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John |
VBA in shared workbook
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 -- Thank you for your time! John "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John "Luke M" wrote: 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? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Very Basic User" wrote: 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! -- Thank you for your time! John |
| All times are GMT +1. The time now is 06:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com