Hello,
Your local Novice here. I am trying to create a log for changes made to a master data workbook. So far I am having some issue with naming the new sheet created by the macro, see code below.
Sub ChangeLog()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Change Submission"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
sheet_name_to_create = Sheet1.Range("C4").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "This sheet already exists!"
Exit Sub
End If
Next
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = sheet_name_to_create
End Sub
_______________________________________
This is the begining of the code, next it will open master file and move a copy of all the changed data to the new sheet which should be named as the date of the change submission.
Please let me know if you need clarification on any details.
Thank you again for all of your help you all are the best.
Your local Novice here. I am trying to create a log for changes made to a master data workbook. So far I am having some issue with naming the new sheet created by the macro, see code below.
Sub ChangeLog()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Change Submission"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
sheet_name_to_create = Sheet1.Range("C4").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "This sheet already exists!"
Exit Sub
End If
Next
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = sheet_name_to_create
End Sub
_______________________________________
This is the begining of the code, next it will open master file and move a copy of all the changed data to the new sheet which should be named as the date of the change submission.
Please let me know if you need clarification on any details.
Thank you again for all of your help you all are the best.