Excel macro to Create Sheets, Copy Data

Microsoft Excel 2003 (full)
April 30, 2010 at 15:53:47
Specs: Windows 7
Hello,

I am new to macros. I am not sure if this can be done, but I hope it can.

I would like to create a macros that will create a new sheet for each day of the week automatically. Then pull certain information from the previous day to carry over. I would like it to automatically generate 5/1/2010, then 5/2/2010, etc. for example.

I would like it to then take those days of the week and compile them into a new sheet labled 5/1 - 5/8 (looking @ my calendar for May). I would like for the info from the days of the week to be compiled into a summary for the week.

I would then like it to compile the data from the weeks of the month into a new sheet labled month (May for example). And again, compile me a summary for the month based on the weeks and days information.

Finally, I would like it to take all the months of the year, and compile that data into a new tab for the year (2010) for example. And the automatically summarize certain data for the entire year.

Then, start over for 2011 doing the same thing. Days, weeks, months, year.

I would like the months of the year to automatically go under the year tab, so that if I click the year tab it exapands to months, if I click on a certain month it expands to weeks and if I click on a certain week in the month it opens the days of that week.

Hope that make sense. Is this possible to do?


See More: Excel macro to Create Sheets, Copy Data

Report •

#1
May 1, 2010 at 15:19:52
Hi,

If you are asking for macros to do all of what you are asking for, then I suggest that you hire a programmer!

Most of what you want is possible in Excel, although some things don't work the way you suggest:
I would like the months of the year to automatically go under the year tab, so that if I click the year tab it expands to months
I don't know what you are thinking of when you refer to tabs, such as 'the year tab'. Are you referring to the worksheet name tab, or are you thinking of custom forms with a series of tabs. Worksheet name tabs don't lend themselves to 'expanding'.

create a new sheet for each day of the week automatically
This will result in 365 worksheets just for one year's data, excluding the month and other summary tabs.

It is far better to start with all your data in one block - on one worksheet, then you can extract data for specific periods from it.

Having daily data on separate sheets makes finding it and analyzing it a lot more difficult, even using visual basic macros. If you have a column containing dates, Excel stores dates as numbers which can be used to find a range of dates. If you have dates on worksheet name tabs, the 'date' is stored as a string, not as an Excel date number, so selecting worksheet names based on text representations of dates becomes quite complex.

If you add 1 to a cell containing 01-Jan-2010, you get 02-Jan-2010, but there is no direct way to add 1 to a worksheet named 01-Jan-2010 to get 02-Jan-2010.

If your monthly data is on separate worksheets, it will be hard to create charts showing monthly data as there are no simple sequences of data to use.

Having months divided into weeks of data will result in lots of 'part-weeks', which is data that has little use.
A single sequence of weeks of data throughout the year results in, at most two 'part-weeks' of data. If done by month you could end up with 24 'part-weeks' of data.

The daily data can be presented as weekly data (the WEEKNUM() function is usefull) and monthly data can be extracted using SUMPRODUCT() to extract and sum data between specific dates.

As you have not used Excel macros before, I suggest that you start by doing the basics with Excel formulas and then use macros to add specific features.

When using formulas, as you find a need to add additional analyses and perhaps more charts, being able to access the data in simple ranges will make it so much easier. If the whole output is dependent on macros, you will also have to become very proficient in understanding and editing other peoples macros - not the easiest thing to do, especially as you will have macros created by different programmers.

I know that much of the above sounds negative, but I say this based on years of analyzing data - and my experience says - keep the raw data together. Big blocks of data can easily be analyzed by smaller periods, but aggregating lots of small pieces of data stored in different places into larger blocks is hard.

Regards


Report •

#2
May 2, 2010 at 18:33:54
Makes sense, thank you for the thorough response!

Report •

#3
May 3, 2010 at 05:04:42
Hi,

Please come back with further questions - I am sure we can offer something positive !!!

I tried this:
Source worksheet named "DailyData"
Headings in Row 1
A1 Date; B1 Week #; C1 Data #1
Column A - A2 A366 contains dates
Enter 01-Jan-2010 in A2 (and format as dd-mmm-yy)
In A3 enter:

=A2+1

Drag A3 down to A366
A366 will show 31-Dec-2010

Cell B2 enter:

=WEEKNUM(A2,2)

Drag down to B366
Column B will contain the week number for each date

Cells C2 to C366 enter first data set
Cells D2 to D366 enter second data set etc ...

In a second worksheet named "WeeklyData"
Headings in Row 1
A1 Week #
B1 Data #1
C1 Data #2 etc ...

Enter numbers 1 in Cell A2 and 2 in Cell A3
Select cells A1 and A2 and drag down to A54
A54 will contain the week # 53

In Cell B2 enter this formula:

=SUMPRODUCT((DailyData!$B$2:$B$366=$A2)*
(DailyData!C$2:C$366))

Drag the formula in Cell B2 down to Cell B54
Cells B2 to B54 will now contain the weekly data, based on the daily data

In a third worksheet named "MonthlyData"
Headings in Row 1
A1 Start on month
B1 End of month
C1 Data #1

Enter 01-Jan-2010 in cell A2
enter this formula in Cell B2:

=EOMONTH(A2,0)

EOMONTH() is not available by default - Go to Tools - Add-Ins...
In the Add-Ins dialog box chek the box alongside 'Analysis ToolPak"
EOMONTH() will now be available
In Cell A3 enter:
=B2+1

In Cell B3 enter:
=EOMONTH(A3,0)

Select cells A3 and B3 and drag them down to row 13
You will now have a series of month date ranges

(The following formulas have been split on to several lines for ease of viewing - you will need to merge each one back into a single line before pasting into the cell's formula bar).

In Cell C2 enter:

=SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366))

Drag this down to C13
You will now have monthly data based on the daily data

In Cell F1 enter "Actual/Estimate"
In Cell G1 enter "Data #1"

In Cell F2 enter:

=IF(SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366<>""))=0,"",IF(SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366<>""))=($B2-$A2+1),"Actual","Estimate 
("&SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366<>""))&")"))

In Cell G2 enter:
=IF(SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366<>""))=0,0,SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366))/SUMPRODUCT((DailyData!$A$2:$A$366>=MonthlyData!$A2)*
(DailyData!$A$2:$A$366<=MonthlyData!$B2)*
(DailyData!C$2:C$366<>""))*($B2-$A2+1))

Select cells F2 and G2 and drag down to F13/G13
You will now have actual data for completed months and estimated data for a partial month (data so far / number of days data * number of days in month). Column F will identify actual or estimated data.

To add a pop-up that shows the 7 days of data in a week add this macro which runs when a cell in column B on the WeeklyData sheet is selected.
On the worksheet "WeeklyData" right-click the name tab at the bottom of the window.
Select 'View code'
Paste the following into the large Visual Basic window:

Option Explicit

Private Sub Worksheet_Deactivate()
Dim n As Integer
'remove any comments when leaving this worksheet
For n = 1 To Worksheets("WeeklyData").Comments.Count
    Worksheets("WeeklyData").Comments(n).Delete
Next n
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

Dim rngCell As Range
Dim intWeek As Integer
Dim strWeek As String
Dim n As Integer

On Error GoTo ErrHnd

'remove any existing comments
For n = 1 To ActiveSheet.Comments.Count
    ActiveSheet.Comments(n).Delete
Next n

'respond to the column that is selected
Select Case Target.Column
Case 2
'a cell in col 2 selected
'get the week number from the column to the left of the selected cell
intWeek = Target.Offset(0, -1).Value
'create the message to go into the comment box
strWeek = ""
For Each rngCell In Worksheets("DailyData").Range("B3:B367")
    If rngCell.Value = intWeek Then
        strWeek = strWeek & rngCell.Offset(0, 1).Text & vbCrLf
    End If
Next rngCell
'create the comment box
Target.AddComment (strWeek)
Target.Comment.Shape.Height = 78
Target.Comment.Shape.Width = 24

Case Else
'do nothing
End Select

Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub

Now try selecting a cell in column B and see the data for that week appear - assuming that you have populated the first column of data in the "DailyData" worksheet

You will see in the code that there is a statement Case 2
This line and the following lines can be duplicated using Case 3, to get a popup for weekly data in column C

like this:

Case 3
'a cell in col 3 selected
'get the week number from the column 2 to the left of the selected cell
intWeek = Target.Offset(0, -2).Value
'create the message to go into the comment box
strWeek = ""
For Each rngCell In Worksheets("DailyData").Range("B3:B367")
    If rngCell.Value = intWeek Then
        strWeek = strWeek & rngCell.Offset(0, 2).Text & vbCrLf
    End If
Next rngCell
'create the comment box
Target.AddComment (strWeek)
Target.Comment.Shape.Height = 78
Target.Comment.Shape.Width = 24

Hope this gives you some ideas.

Regards


Report •
Related Solutions


Ask Question