Sub cmdCopy()
Dim wsDst As Worksheet, wsSrc As Worksheet, tblrow As ListRow
Dim Combo As String, sht As Worksheet, tbl As ListObject
Dim lastrow As Long, DocYearName As String
Application.ScreenUpdating = False
'assign values to variables
Set sht = Worksheets("Home")
With sht
Set tbl = .ListObjects("tblCosting")
[color=red]For Each tblrow In tbl.ListRows
If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
MsgBox "All values haven't been entered"
Exit Sub
End If
Next tblrow[/color]
For Each tblrow In tbl.ListRows
Combo = tblrow.Range.Cells(1, 26).Value
'lastrow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1 'number of first empty row in column A of Combo
If tblrow.Range.Cells(1, 6).Value = "Anglicare Western" Then
DocYearName = tblrow.Range.Cells(1, 37).Value
Else
DocYearName = tblrow.Range.Cells(1, 36).Value
End If
Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
With wsDst
'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
tblrow.Range.Resize(, 10).Copy
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
'This should go to the 15th column in the current row, i.e. column O, and copy that column and the next 2 columns, i.e. O:Q, to column K on the destination sheet.
tblrow.Range.Offset(, 14).Resize(, 3).Copy
.Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
'Similarly this should copy columns AD:AF from the table to column N on the destination sheet.
tblrow.Range.Offset(, 29).Resize(, 3).Copy
.Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
'Sort rows based on date
Rows("3:1000").Select
Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Clear
Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Add Key:=Range("A4:A1000"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Workbooks(DocYearName).Worksheets(Combo).Sort
.SetRange Range("A3:AJ1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Next tblrow
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub