Macro to recognise dates

MikeNZ · Sep 28, 2011

I am wondering if there is a way for a macro to recognise a certain day in one cell and export data from different cells to a seperate spreadsheet?

So it would need to read the date in cell A1, recognise that it is a Monday then export the data in cell B1 to a seperate spreadsheet.

Hope this makes sense, all help very much appreciated.

Andrew Poulsom · Sep 28, 2011

Example:

Code:

Sub Test()
    Dim TheDay As String
    TheDay = Format(Range("A1").Value, "dddd")
    MsgBox TheDay
End Sub

MikeNZ · Sep 28, 2011

Which spreadsheet would I insert this macro into and how would I determine which spreadsheet to send it to?

Andrew Poulsom · Sep 28, 2011

The code I posted just demonstrated how to return the day of the week from a date. You weren't specific about the source and destination of your export so I didn't attempt to answer that part.

MikeNZ · Sep 28, 2011

Apologies, I will try to explain further. Unfortunately I can't use the HTML maker to insert workbook because I'm currently using a work computer!

I have two workbooks (book1 and book2). Book1 has the date in cell A1 which changes each day, in cells B3:B10 there are a list of times, with data in cells C3:C10. Book2 has several sheets labelled for different days of the week. On each sheet the time is in cells B1:I1 with the date in cell A1.

I would need the macro to recognise the date in book1 cellA1 and send the data from C3:C10 to the cells in book 2 with the corresponding time.

Hope this makes sense, think I'm confusing myself!

Andrew Poulsom · Sep 28, 2011

To post a snapshot of your data put borders around it, copy it in Excel and paste it into a reply. You need to select 'Enhanced Interface - Full WYSIWYG Editing' in 'Message Editor Interface' under User CP|Edit Options (menu towards top of screen on Board).

MikeNZ · Sep 29, 2011

I've pasted an example of what the workbooks look like. The first is book1, the second is one sheet named "Saturday" in book2. Book2 will have seven sheets named by day.

<TABLE style="WIDTH: 317pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=422><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=64></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 125pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=166>

A

</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=64>

B

</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=64>

C

</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=64>

D

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 height=20>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>

Saturday, 22 January 2011

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>

Time

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>

Win

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 height=20>

3

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>