VBA Code Copy Paste a range to the bottom of another dynamic sheet and range

[paulcherianc]

I have created a macro as follows:

Can you please help me to script this to VBA

Sub CopyPaste()

    Sheets("Report Footer").Select
    Range("A1:AA23").Select
    Selection.copy
    Sheets("PCS").Select ' -  it can be a dynamic sheet
    Range("J1").Select
    Selection.End(xlDown).Select
    Range("J13").Select '-  it can be a dynamic cell
   
End Sub

 

[paulcherianc]

Its fine. I have coded it.

Dim LastRow As Long

LastRow = Cells(Rows.Count, 10).End(xlUp).Row
   
    With Cells(LastRow + 1, "A")
         .Value = "A"
         .Offset(0).Interior.Color = vbRed
         .Offset(1).Value = "DO"
         .Offset(1).Interior.Color = vbYellow
         .Offset(2).Value = "PH"
         .Offset(2).Interior.Color = RGB(153, 102, 255)
         .Offset(3).Value = "R"
         .Offset(3).Interior.Color = vbYellow
         .Offset(4).Value = "S"
         .Offset(4).Interior.Color = vbGreen
         .Offset(5).Value = "TR"
         .Offset(5).Interior.Color = RGB(255, 255, 153)
         
                         
    End With
   
    With Cells(LastRow + 1, "B")

         .Value = "Absent"
         .Offset(1).Value = "Day Off"
         .Offset(2).Value = "Public Holiday"
         .Offset(3).Value = "Release From Site"
         .Offset(4).Value = "Sick"
         .Offset(5).Value = "Transfer"
       
                             
    End With


    With Range(Cells(LastRow + 1, "A"), Cells(LastRow + 5, "B"))
         .Borders.LineStyle = xlContinuous
         .Borders(xlInsideVertical).LineStyle = xlContinuous
         .Offset(1).Borders.LineStyle = xlContinuous
         .Offset(1).Borders(xlInsideVertical).LineStyle = xlContinuous