dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have a spreadsheet that copies entries to other sheets depending on the date. It copies perfectly to every sheet. There are sheets ranging from July 2018-June 2019. I have an entry for every month between the two dates. The only thing wrong with the copy is in the august 2018 sheet, it won't copy columns M-O from tblCosting on the home sheet to columns K-M of august 2018 sheet. There is data in every row of every cell of tblCosting. This doesn't make sense to me as the code is not monthly specific so I have no idea how to debug it.
The code I run is:
and......
Can someone help me please?
The code I run is:
Code:
Sub cmdCopyo()
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim tblrow As ListRow
Dim Combo As String
Dim sht As Worksheet
Dim tbl As ListObject
Application.ScreenUpdating = False
'assign values to variables
Set sht = Worksheets("Home")
With sht
Set tbl = .ListObjects("tblCosting")
For Each tblrow In tbl.ListRows
Combo = Format(tblrow.Range.Cells(1, 1), "mmmm yyyy")
Set wsDst = Sheets(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
End With
Next tblrow
Call SortDates
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
and......
Code:
Sub SortDates()
'
' sortDates Macro
Worksheets("home").Unprotect Password:="costings"
'Set up your variables and turn off screen updating.
Dim iCounter As Integer
Dim Combo As String
Combo = Worksheets("Home").Range("Y5")
Application.ScreenUpdating = False
'Sort the rows based on the data in column C
' Sheets(Combo).Sort Key1:=Range("A4"), _
Order1:=xlAscending, Header:=xlYes
'Clear out the temporary sorting value in column C, and turn screen updating back on.
'Columns(3).ClearContents
Application.ScreenUpdating = True
'Worksheets("home").Protect Password:="costings"
End Sub
Can someone help me please?