Copy info from one excel sheet to another sheet [Solved/Closed]

Report
Posts
3
Registration date
Wednesday December 10, 2014
Status
Member
Last seen
December 12, 2014
-
Posts
3
Registration date
Wednesday December 10, 2014
Status
Member
Last seen
December 12, 2014
-
Hello,

I am respectfully asking for assistance on an issue i am having trying to write a macro for a form that i am currently working on. I have sheet 1 that i call "FORM" that I enter information into. I am trying to create a macro to copy the info that i put on the FORM onto sheet 2 "LOG". When moving the information from the FORM to the LOG, I would like to log it in order. Then after it is entered into the LOG, i would like to protect the LOG so that it cannot be changed until the end of each year. I would also like the FORM to print after i press a command button on the FORM to enter the information on to the LOG. I have the form and log created and have attempted to work with recording my own macro but it just doesn't seem to be working properly.

This is what i have for my macro, but i get this response in a pop up:

Runtime error '1004'
Application-defined or object-defined error

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveSheet.Unprotect
Sheets("FORM").Select
ActiveCell.Offset(-5, 34).Range("A1:N1").Select
Selection.copy
Sheets("LOG").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("FORM").Select
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(3, -30).Range("A1:AB1").Select
End Sub

Thank you so much in advance for your assistance!

2 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Sstanger,

I may have something for you but I just need to clarify a few things.

1) You wish to transfer data from the "Form" sheet to the "Log" sheet.

2) You wish to print the "Form" sheet at the same time the data is transferred to the "Log" sheet. It would be best to use a separate button for this command because if it is included in the same macro, it will print every time you make an entry. I assume that you would only want to print the "Form" on completion of all data entry.

3) You wish to protect the "Log" sheet after completing your entries. The easiest way to protect worksheets or workbooks is to use the inbuilt protect function. Go to the Review tab in the ribbon, Changes group, select "Protect Sheet". Make sure that the "Protect worksheet and contents of locked cells" box is ticked. Then enter your password in the next box. You may be prompted to re-enter your password to confirm it. Save and close the work book. On re-opening it, the "Log" sheet should be read only.

Also, to clarify your requirements, please upload a sample of your work book but desensitise any confidential information. Use a file sharing site such as DropBox or SpeedyShare.

Regards,
vcoolio.
Posts
3
Registration date
Wednesday December 10, 2014
Status
Member
Last seen
December 12, 2014

Thank you vcoolio for your response, but I was able to figure it out with a lil more research!