Hi, I'm trying to develop a module that could help us in the office to have a list with all the monthly task each member of the team has to perform. I've lets say:
I've a file call "Input". From cells O11:AJ200 empty cells that have should be locked if the value in cells AL11:BG200 is "False"
So the cells in within the range O11:AJ200 are unlock, as soon as the task, which is define in column "A" as a entry code, is complete, the member of the tema has to write any value in the cell (it will be nice with VBA that is converted to Monotype Sorts font and the value changed to number 4 in order to show a ticking mark) and automatically a log should be generated into another file.
The other file, let's call it Output, should show in column A: Date/Time and in cell B the name of the user. In column C should show the entry code of the file "Input" and cell "A" concatenated with the name of the column where it was (Range "AL10:BG10"). That means, that when in the same row there are many cells with a ticking mark, in Output there should be the same number of rows including the date, name and entry.
The issue is, the information should come from different worksheets, which I've defined as activeSheet.
Here is something I've tried:
Sub UpdateLogWorksheet()
Dim logWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = ActiveWorkbook.Activesheet
Set logWks = Worksheets("Concentrate")
oCol = 3 'Entry info is pasted on data sheet, starting in this column
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("BJ11:CE200")
With logWks
nextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End With
With logWks
'enter date and time stamp in record
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "B").Value = Application.UserName
'copy the Entry data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, oCol).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
Do you have any idea?
I've a file call "Input". From cells O11:AJ200 empty cells that have should be locked if the value in cells AL11:BG200 is "False"
So the cells in within the range O11:AJ200 are unlock, as soon as the task, which is define in column "A" as a entry code, is complete, the member of the tema has to write any value in the cell (it will be nice with VBA that is converted to Monotype Sorts font and the value changed to number 4 in order to show a ticking mark) and automatically a log should be generated into another file.
The other file, let's call it Output, should show in column A: Date/Time and in cell B the name of the user. In column C should show the entry code of the file "Input" and cell "A" concatenated with the name of the column where it was (Range "AL10:BG10"). That means, that when in the same row there are many cells with a ticking mark, in Output there should be the same number of rows including the date, name and entry.
The issue is, the information should come from different worksheets, which I've defined as activeSheet.
Here is something I've tried:
Sub UpdateLogWorksheet()
Dim logWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = ActiveWorkbook.Activesheet
Set logWks = Worksheets("Concentrate")
oCol = 3 'Entry info is pasted on data sheet, starting in this column
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("BJ11:CE200")
With logWks
nextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End With
With logWks
'enter date and time stamp in record
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "B").Value = Application.UserName
'copy the Entry data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, oCol).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
Do you have any idea?