VBA Macro to Copy from Table & Create Matrix style view

J

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
Hi All,

I currently have a spreadsheet with the following sheets which are tables, I also have a worksheet for every month and creating a monthly view of each day in their associated worksheet, I basically need a VBA macro to use the below information to construct a matrix such as below depending on the month listed, in each monthly worksheet I will also have a field which identifies the current year, this will also need to be used because the user will be allowed to choose the year to display from a drop-down list. In the matrix the days will need to be coloured depending on status. G = Green, B = Red, W = White (White = hasn't completed yet), also is it possible for the macro to enter the reason as a comment for a day marked as 'B'?

Hope I've explained it clearly, feel free to ask if I haven't, thanks in advance :)

- Server_Listing
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Server Name[/TD]
[TD]Client:Customer Name[/TD]
[TD]Server Type[/TD]
[TD]Location[/TD]
[TD]IP Address[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]ServerAB[/TD]
[TD]Customer 1[/TD]
[TD]ArcServe[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD]ServerXY[/TD]
[TD]Customer 2[/TD]
[TD]ArcServe[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD]Server02[/TD]
[TD]Customer 3[/TD]
[TD]ArcServe[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD]Server03[/TD]
[TD]Customer 3[/TD]
[TD]ArcServe[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD]Test[/TD]
[/TR]
</tbody>[/TABLE]

- Exceptions
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Title[/TD]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]Client Server[/TD]
[TD]Error Code[/TD]
[TD]Sequence[/TD]
[TD]Reference[/TD]
[TD]Reason[/TD]
[TD]Current Status[/TD]
[TD]SLA Exempt[/TD]
[TD]Day_Calc[/TD]
[TD]Month_Calc[/TD]
[TD]Year_Calc[/TD]
[/TR]
[TR]
[TD]Customer 3 - Server03[/TD]
[TD]Customer 3[/TD]
[TD]8/08/2013[/TD]
[TD]Server03[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test Reason[/TD]
[TD]Good[/TD]
[TD]TRUE[/TD]
[TD]8[/TD]
[TD]August[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Customer 2 - ServerXY[/TD]
[TD]Customer 2[/TD]
[TD]9/08/2013[/TD]
[TD]ServerXY[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test Reason[/TD]
[TD]Bad[/TD]
[TD]FALSE[/TD]
[TD]9[/TD]
[TD]August[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Customer 1 - ServerAB[/TD]
[TD]Customer 1[/TD]
[TD]16/08/2013[/TD]
[TD]ServerAB[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test Reason[/TD]
[TD]Bad[/TD]
[TD]TRUE[/TD]
[TD]16[/TD]
[TD]August[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Customer 3 - Server02[/TD]
[TD]Customer 3[/TD]
[TD]9/08/2013[/TD]
[TD]Server02[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test Reason[/TD]
[TD]Bad[/TD]
[TD]TRUE[/TD]
[TD]9[/TD]
[TD]August[/TD]
[TD]2013[/TD]
[/TR]
</tbody>[/TABLE]

- Worksheet August (Matrix View) Cell B8 = 2013 [Button Run VBA]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Server[/TD]
[TD]Server Type[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]Customer 1 (merged cells)[/TD]
[TD]ServerAB[/TD]
[TD]ArcServ[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Customer 2 (merged cells)[/TD]
[TD]ServerXY[/TD]
[TD]ArcServ[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Customer 3 (merged cells)[/TD]
[TD]Server02[/TD]
[TD]ArcServ[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3 (merged cells)[/TD]
[TD]Server03[/TD]
[TD]ArcServ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Attached a pic for matrix:
1150984_10151741914421132_1463803186_n.jpg
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
172
SBExcel123
S
S
  • Question Question
VBA Macro for creating multiple sheets on command
Replies
2
Views
350
OaklandJim
O
BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
192
BigPaws
BigPaws
C
  • Question Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
485
chittunoo
C
S
  • Solved
VBA copy template sheet to new monthly workbook with daily sheets tabname and header A2:J2 named as "dd mm yyyy" + autosave filename predefined
Replies
4
Views
779
mumps
mumps

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

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