Copying data from a year tab and pasting into month tabs

B-radK

Board Regular
Joined
Apr 1, 2010
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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.

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 Sub

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.
 

Attachments

  • Year data.png
    Year data.png
    9.2 KB · Views: 6

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sorry Team, I would like to add, that I could just use formulae to do this, but there will be occurrences whereby there will be empty rows, so I would like the copied data to be pasted into the next available row.

I can either do the above (first post) or the formulae way and then remove empty rows by VBA, but it would be nice to have code to do the whole lot.

Thanks,
Brad.
 
Upvote 0
Hello Team, any assistance would be helpful. Thank you.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top