excel: create calendar worksheet based on column data

Hi hope you guys can help: I have a major headache and hopefully you guys will assist me.
i am using excel 2013 and i see i can insert calendar workeets but what i need is  each month on its own worksheet with data added from two columns

the date data is
Date Evaluation Due
29/10/2014
29/10/2014
29/10/2014
29/10/2014
05/03/2015
08/05/2015
08/05/2015
08/05/2015
08/05/2015
15/05/2015
22/05/2015
25/05/2015
25/05/2015
26/06/2015
16/07/2015
17/07/2015
17/07/2015
17/07/2015
17/07/2015
17/07/2015
17/07/2015
17/07/2015
21/07/2015
23/07/2015
24/07/2015
29/07/2015
04/08/2015
the subject column is
Id
6933
6936
7950
6935
7515
7854
7855
7857
7856
7801
7942
7932
7861
8181
8213
8320
8315
8314
8319
8180
8201
8206
8132
7304
8353
8438
8261


Any ideas where to start...
LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Do you want some sort of planner, like a lookahead that tells you want needs doing week by week?
James MurrellProduct SpecialistAuthor Commented:
yes as those columns get updated regularly
Roy CoxGroup Finance ManagerCommented:
I have a five day lookahead with an input sheet for tasks to be entered and a calendar sheet that shows for the next 5 days ahead. It needs cleaning up though because I used for a company that I worked for.

I can post it later.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

James MurrellProduct SpecialistAuthor Commented:
Sounds great: hopefully i change add more than the 5 days thanks Roy_Cox
Roy CoxGroup Finance ManagerCommented:
The one that I originally mentioned will take a while to clean up, but here's one that I think will be better. I created it a couple of years ago. Any problems let me know, I would appreciate feedback on it.

Appointmets Manager
James MurrellProduct SpecialistAuthor Commented:
sorry it does not work with my excel 2013 I enable marcos etc  mcalform warning
Roy CoxGroup Finance ManagerCommented:
It was developed in Excel 2013 and I have just opened it with Excel 2016.

Have you got the calendar icon in the same folder as the workbook? I'll open it with 2013 and get back to you
Roy CoxGroup Finance ManagerCommented:
Just downloaded the same files above and opened them on my work computer using Excel 2013 with no problems. You need to extract both files to the same folder
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If your objective is to populate sheets with each month and its data from the Data sheet only, please find the attached workbook, where you will find a button named Click Here, you may click that button to create sheet for each month with its data.

So once you add new data on the Data sheet in the example workbook, just click the button and that data will transferred to its relevant month sheet.

Though clicking the button each time after adding new data to transfer it on its relevant month sheet is not ideal. The ideal approach in this case would be to use worksheet change event so that once you fill the column B with Id, the data will be automatically transferred to its relevant sheet.

Please let me know if it helps you in anyway.
Transferred-Data-To-Month-Sheets.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
I don't see why you need VBA to populate sheets when formulas will do the job.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Roy!

It depends on how much data you have and how will it grow in future as well.
Roy CoxGroup Finance ManagerCommented:
When doing something like this I always make the Calendar dynamic so that you don't pull through past data unless you require it.
James MurrellProduct SpecialistAuthor Commented:
Roy_cox - must be because i am running windows 10: i get error with or with image... hey ho

sktneer - I like it , but look at macro i change values to y2 instead of a2 and ai instead of b2 and it crashes.  could you comment in macro so I can learn?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi James,

Tried my best to comment the code to make it easy for you to change it as per your requirement.
If still have any doubt about any line of code, please let me know, I will try to clarify it.

Here is the code...

Sub CreateMonthSheets()
Dim Sws As Worksheet, Dws As Worksheet
Dim Slr As Long
Dim Srng As Range, Cell As Range

Application.ScreenUpdating = False

Set Sws = Sheets("Data")            'Source Sheet, Data Sheet in this example
Slr = Sws.Cells(Rows.Count, 1).End(xlUp).Row        'Slr is the last row with data in Date Column A. Here 1 stands for col. A index number so if the Date column is B, 1 should be changed to 2.
Set Srng = Sws.Range("A2:A" & Slr)      'Srng assumes that the Dates are in col. A starting from Row2

For Each Cell In Srng
    If Sws.Cells(Cell.Row, "AA") <> "Y" Then        'The code checks if the corresponding cell AA doesn't have a Y in it as when the row is copied first time the code puts a "Y" in the cell AA of that row, therefore it would not be copied next time.
        On Error Resume Next
        Set Dws = Sheets(Format(Cell, "mmm-yy"))        'This will be name of the month sheet and if the month sheet is not present it will produce an error and the control passes to the next IF block assuming Err<>0 and will create a new month sheet to have data
        If Err <> 0 Then
            Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = Format(Cell, "mmm-yy")   'Adds a worksheet and name it with the mmm-yy format of the date
            Set Dws = ActiveSheet
            Dws.Range("A1").Value = "Date Evaluation Due"       'The next few lines place the columns headers in the month sheets and format them
            Dws.Range("B1").Value = "Id"
            Dws.Range("A1:B1").Font.Bold = True
            Dws.Range("A1:B1").HorizontalAlignment = xlCenter
        End If
        On Error GoTo 0
        '1 in the below line is the column index number of col. A and 2 is the column index number of col. B, change them as per your requirement.
        'Dws.Range("A" & Rows.Count).End(3)(2) will be the first empty cell in col. L, change it if you want to change the destination column
        Sws.Range(Sws.Cells(Cell.Row, 1), Sws.Cells(Cell.Row, 2)).Copy Dws.Range("A" & Rows.Count).End(3)(2)
        Sws.Cells(Cell.Row, "AA") = "Y"     'Once a row is copied to its relevant month sheet, the code puts a "Y" in the corresponding cell in col. AA to confirm that the row has been transferred to the months sheet.
        Application.CutCopyMode = 0
        Dws.UsedRange.Columns.AutoFit
    End If
Next Cell

Sws.Activate
Application.ScreenUpdating = True
MsgBox "Data has been transferred to the Months Sheets successfully.", vbInformation, "Done!"
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can also change the code as below.....

Slr = Sws.Cells(Rows.Count, "Y").End(xlUp).Row        'Slr is the last row with data in Date Column Y. 
Set Srng = Sws.Range("Y2:Y" & Slr)      'Srng (Range) assumes that the Dates are in col. Y starting from Row2

Open in new window


Similarly....

Sws.Range(Sws.Cells(Cell.Row, "Y"), Sws.Cells(Cell.Row, "Z")).Copy Dws.Range("A" & Rows.Count).End(3)(2)

Open in new window

Roy CoxGroup Finance ManagerCommented:
I am running Windows 10 with Office 2016 and the workbook works perfectly/ What error do you get exactly?.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.