Macro to fill data from sheet to another sheet-CodePudding

Hi I have created macro where it opens the sheet based on user input, what I need is once the new sheet is opened I have some fields where user need to fill those data(Different subjects marks) and calculate the percentage using formula then I need to fill those data to another sheet named "Data" without overwriting previous data?. Please suggest how to add data without overwriting in vba.

Sub open_sheet()
Dim sourcesheet As Worksheet
Dim ClassA As Worksheet
Dim ClassB As Worksheet
Dim ClassC As Worksheet

Set sourcesheet = Sheets("Main")
Set ClassA = Sheets("Class A")
Set ClassB = Sheets("Class B")
Set ClassC = Sheets("Class C")

If sourcesheet.Range("Class").Value = "Class A" Then
Worksheets("Class A").Activate
ElseIf sourcesheet.Range("Class").Value = "Class B" Then
Worksheets("Class B").Activate
Else:
Worksheets("Class C").Activate
End If
End Sub

CodePudding user response：

Copy Cell Values to Another Worksheet

Option Explicit

Sub CopyData()
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the source worksheet.
    Dim sws As Worksheet: Set sws = wb.Sheets("Main")
    ' Reference the source range (the values from these cells will be copied).
    Dim srg As Range: Set srg = sws.Range("A3,B4,C5")
    
    ' Retrieve the destination worksheet name.
    ' Hopefully you have created a drop down to easily select the class.
    Dim dName As String: dName = sws.Range("Class").Value
    
    ' Late at night (tired), a final check could become a life saver:
    Dim Msg As Long
    Msg = MsgBox("This will copy to """ & dName & """." & vbLf & vbLf _
        & "Are you sure?", vbQuestion   vbYesNo)
    If Msg = vbNo Then Exit Sub
    
    ' Reference the destination worksheet.
    Dim dws As Worksheet: Set dws = wb.Sheets(dName)
    If dws.FilterMode Then dws.ShowAllData ' 'Find' will fail if 'dws' filtered
    
    ' Reference the first (available) destination cell.
    
    Dim dCell As Range ' First Destination Cell
    
    With dws.UsedRange
        Dim dlCell As Range ' Last Cell
        Set dlCell = .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If dlCell Is Nothing Then Exit Sub ' empty worksheet
        Set dCell = dws.Cells(dlCell.Row   1, "A") ' below last in column 'A'
    End With
    
    ' Copy the values from the source to the destination cells.
    
    Dim sCell As Range
    
    For Each sCell In srg.Cells
        dCell.Value = sCell.Value
        Set dCell = dCell.Offset(, 1) ' next, adjacent to the right
    Next sCell
    
    MsgBox "Data copied.", vbInformation

End Sub

CodePudding user response：

In general, here's a way to append info to a table. I would just put your average calculations in the table total row.

Option Explicit
Sub FillNewRow1()
    
    Dim Class_A As Worksheet
    Dim ClassName As String
    Dim DataRange
    Dim lRow As Long
    
    ClassName = Worksheets("Master").Range("B2").Value
    Set Class_A = ThisWorkbook.Worksheets(ClassName)
    DataRange = Worksheets("Master").Range("B5:B8")
    
    lRow = Class_A.Range("A" & Rows.Count).End(xlUp).Row   1
    Class_A.Range("A" & lRow).Resize(1, UBound(DataRange, 1)).Value = _
    Application.Transpose(DataRange)
    
End Sub

But seeing as we have no idea what your source od destination data look like that's the best help I can give.