Hi Team,
I have a tab called "Year", which has all the dates of the year from 1 Jan 2024 to 31 Dec 2024, starting in cell C5 (1/1/24) and dragged horizontally to fill series all the way to cell ND5 (31/12/24).
Below these dates in row 6 downwards contains certain data. In this case, single letters, "O" for example.
I have 12 other tabs, labelled "Jan", "Feb", "Mar",..... "Nov", "Dec".
In these tabs, the dates are lined up horizontally, from cell C5 and stretches out until the end date of the respective month.
I have tried to write some code that copies the data from the "year" tab underneath the dates, and opens each month tab and pastes the data directly under the corresponding date in that tab, on the next available (empty) line.
So far, I have only been able to paste just the January date data from the "year" tab and paste that in all of the months. This is wrong, as the Jan data is written into the Feb, Mar,... Nov and Dec tabs.
Could I please have some assistance with my code?
I also need to add to the code, whereby in the "year" tab, in column A and B, I have data which corresponds to the same line of data which needs to be copied and pasted into column A and B of the respective month tabs.
If this could also be incorporated, that would be very much appreciated.
Thanks,
Brad.
The attached screenshot is where I have data in Col A and B (Brad, Surveyor and Tom, Engineer) which needs to be pasted into Jan and Feb tables respectively, along with the corresponding data in the same row.
Thanks team, appreciate the support.
Cheers,
Brad.
I have a tab called "Year", which has all the dates of the year from 1 Jan 2024 to 31 Dec 2024, starting in cell C5 (1/1/24) and dragged horizontally to fill series all the way to cell ND5 (31/12/24).
Below these dates in row 6 downwards contains certain data. In this case, single letters, "O" for example.
I have 12 other tabs, labelled "Jan", "Feb", "Mar",..... "Nov", "Dec".
In these tabs, the dates are lined up horizontally, from cell C5 and stretches out until the end date of the respective month.
I have tried to write some code that copies the data from the "year" tab underneath the dates, and opens each month tab and pastes the data directly under the corresponding date in that tab, on the next available (empty) line.
So far, I have only been able to paste just the January date data from the "year" tab and paste that in all of the months. This is wrong, as the Jan data is written into the Feb, Mar,... Nov and Dec tabs.
Could I please have some assistance with my code?
I also need to add to the code, whereby in the "year" tab, in column A and B, I have data which corresponds to the same line of data which needs to be copied and pasted into column A and B of the respective month tabs.
If this could also be incorporated, that would be very much appreciated.
Thanks,
Brad.
VBA Code:
Sub CopyDataToMonths()
Dim wsJan As Worksheet
Dim wsYear As Worksheet
Dim monthNames As Variant
Dim i As Integer
Dim lastRowJan As Long
Dim lastRowYear As Long
monthNames = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
For i = LBound(monthNames) To UBound(monthNames)
Set wsJan = ThisWorkbook.Sheets(monthNames(i))
Set wsYear = ThisWorkbook.Sheets("year")
lastRowJan = wsJan.Cells(wsJan.Rows.Count, "C").End(xlUp).Row
lastRowYear = wsYear.Cells(wsYear.Rows.Count, "C").End(xlUp).Row
wsYear.Range("C6:ND" & lastRowYear).Copy wsJan.Range("C" & lastRowJan + 1)
Next i
End SubThe attached screenshot is where I have data in Col A and B (Brad, Surveyor and Tom, Engineer) which needs to be pasted into Jan and Feb tables respectively, along with the corresponding data in the same row.
Thanks team, appreciate the support.
Cheers,
Brad.
