Copy info from one excel sheet to another sheet

Solved/Closed
sstanger Posts 3 Registration date Wednesday December 10, 2014 Status Member Last seen December 12, 2014 - Dec 11, 2014 at 04:08 PM
sstanger Posts 3 Registration date Wednesday December 10, 2014 Status Member Last seen December 12, 2014 - Dec 12, 2014 at 12:52 PM
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

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Dec 12, 2014 at 08:07 AM
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.
0
sstanger Posts 3 Registration date Wednesday December 10, 2014 Status Member Last seen December 12, 2014
Dec 12, 2014 at 12:52 PM
Thank you vcoolio for your response, but I was able to figure it out with a lil more research!
0