copy the last row of multiple cells to other worksheet

May 17, 2013 at 08:18:53
Specs: Windows 7
I'm trying to create a macro to copy the last row of data from individual cells from one worksheet to another here is what I have so far.

[]Sub UpdateLogWorksheet()

Dim DataLogEcnWks As Worksheet
Dim RptEcnCmpltWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim rngC As Range
Dim myCopy1 As String
Dim myCell As Range

'cells to copy from DataLogEcn sheet - some contain formulas
myCopy1 = "A2,C2,O2,CG2"

Set rngC = Range(myCopy1)

Set DataLogEcnWks = Worksheets("DataLogEcn")
Set RptEcnCmpltWks = Worksheets("RptEcnCmplt")

With RptEcnCmpltWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row

End With

With DataLogEcnWks
Set rngC = Range(myCopy1)

For Each myCell In rngC.Cells
RptEcnCmpltWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell

End With

End Sub

{}


See More: copy the last row of multiple cells to other worksheet

Report •

#1
May 17, 2013 at 12:30:00
Solved

Sub UpdateLogWorksheet()
    
    Dim DataLogEcnWks As Worksheet
    Dim ReportCompleteLogWks As Worksheet
    Dim OpenEcnLogWks As Worksheet
    Dim ReportOpenLogWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim rngC As Range
    Dim rngD As Range
    Dim myCopy1 As String
    Dim myCopy2 As String
    Dim myCell As Range

    'cells to copy from DataLogEcn worksheet - some contain formulas
    myCopy1 = "A2,C2,O2,CG2"

    'cells to copy from OpenEcnLog worksheet - some contain formulas
    myCopy2 = "A2,C2,E2,O2"

Set rngC = Worksheets("DataLogEcn").Range(myCopy1)
Set rngD = Worksheets("OpenEcnLog").Range(myCopy2)

    Set DataLogEcnWks = Worksheets("DataLogEcn")
    Set ReportCompleteLogWks = Worksheets("ReportCompleteLog")
    Set OpenEcnLogWks = Worksheets("OpenEcnLog")
    Set ReportOpenLogWks = Worksheets("ReportOpenLog")

    With ReportCompleteLogWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row
        
        End With

    With ReportOpenLogWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row

End With

        With DataLogEcnWks
        Set rngC = Range(myCopy1)
        oCol = 1
        For Each myCell In rngC.Cells
            ReportCompleteLogWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
        
        End With

        With OpenEcnLogWks
        Set rngC = Range(myCopy2)
        oCol = 1
        For Each myCell In rngC.Cells
            ReportOpenLogWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    
    End With
        
End Sub




Report •
Related Solutions


Ask Question