Snasui Excel Forum

Code: Select all

Sub Save_reportINV()

'Find the last used row in both sheets and copy and paste data below existing data.
    Dim lCopyLastRow2 As Long
    Dim lDestLastRow2 As Long


'Set variables for copy and destination sheets
  Set wsSheet2 = Workbooks("copy วางต่อ row สุดท้าย.xlsm").Worksheets("report")
  Set wsSheet5 = Workbooks("copy วางต่อ row สุดท้าย.xlsm").Worksheets("DATA")
  Set wsSheet6 = Workbooks("copy วางต่อ row สุดท้าย.xlsm").Worksheets("center")


'Set1
  lCopyLastRow2 = wsSheet5.Cells(wsSheet5.Rows.Count, "A").End(xlUp).Row
  lDestLastRow2 = wsSheet2.Cells(wsSheet2.Rows.Count, "A").End(xlUp).Offset(1).Row
  wsSheet5.Range("A2:B" & lCopyLastRow2).Copy
  wsSheet2.Range("A" & lDestLastRow2).PasteSpecial Paste:=xlPasteValues
  
  
'Disable marching ants around copied range
    wsSheet5.Application.CutCopyMode = False


wsSheet6.Range("B4").Select
End Sub

Code: Select all

Sub SaveR()
Dim r As Range
Set r = Sheets("center").Range("B4", Sheets("center").Cells(Rows.Count, 2).End(xlUp))
    With Sheets("report").Cells(Rows.Count, 2).End(xlUp)(2).Resize(r.Count)
        .Value = r.Value
        .Offset(, -1).Formula = "=counta(B$2:B" & .Row & ")"
        .Offset(, -1).Value = .Offset(, -1).Value
    End With
End Sub