I have now a input sheet with a Macro button that copies the input data into another worksheet.
the input sheet look like this:
Name: [ ]
Score (math): [ ]
score (art): [ ]
score(gym): [ ]
After the input and clicking a button, it now copies the input in one row: name, score (math), score (art), score(gym)
But I want a entry in three rows:
Name, Score, Course
XX , ##1 , math
XX , ##2 , art
XX , ##3 , gym
so, the macro need to copy the field Name three times, with the same value, and the numbers together with course names seperately into three rows.
in the following code cells F7,F8,F9 contains numbers and C7,C8,C9 are different criteria, C4,C5 are general text.
Please help. Thanks!
Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myInput As String
Dim myCell As Range
'cells to copy from Input sheet - some contain formulas
myCopy = "C4,C5,C7,C8,C9,F7,F8,F9"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("SubData")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mmddhhmmss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
Range("C7:C9").Select
Range("C7").Activate
Selection.ClearContents
Range("C4").Select
End With
On Error GoTo 0
End Sub
Bookmarks