Excel Extracting Dates

J

JAMhome

New Member
Joined
Apr 28, 2011
Messages
41
Hi

I have a summary sheet with many records. I want to be able to extract records based on certain dates from the summary into another worksheet. Is there any code that will do this automatically? Hope this is enough information.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hi JAMhome,

If you just want to extract records with a specific date in a certain column from the Summary sheet to another sheet, then using Excel's AutoFilter with copy should do the job easily.

However, if you need something special, such as extracting data within a range of dates, or a list of dates, then a simple macro could do it. But to get help with that macro you would need to be much more specific about your sheet format (e.g., what column are the dates in, do you need a range of dates or list of dates?).

Damon
 
Upvote 0
Hi Damon

I need a list of dates. The data being collected in the summary sheet are time sheets over a period of time. The dates that need to extracted out are usually holiday times. For example, extracted time is December 1, 2, 3, 8, 9, 10, 15-January 1st. The errors occur when the wrong dates are extracted so I was trying to automate as much as possible with folks who are not sauvy with Excel.

Jam
 
Upvote 0
Hi again JAMhome,

Am I correct then in assuming that you have a table somewhere in your workbook where all these dates are listed? If not, how or where do you specify this list of dates?

Damon
 
Upvote 0
Hi Damon

I do not have these dates listed elsewhere in the workbook. I am open to any suggestions for automating this process. To explain further, there are 85 agencies submitting summaries of employees time for reimbursement. The span of time can be a five months of data. Then the dates I gave you need to be extracted for other purposes. The fields are date, name, hours, and rate.

Jam
 
Upvote 0
Hi JAMhome,

Okay, here is a macro that I believe does what you want. Before you run this macro you will have to create a range somewhere in your workbook and name it "FilterDates" (can be on a separate worksheet). The first column of this range must contain all the dates you want to extract from the Summary sheet. These dates must be standard Excel dates (month, day, and year, and with no time portion), and it is also assumed that the dates in the summary sheet are standard Excel dates. You can enter or display these dates in any date format (doesn't have to be the same format as displayed in the Summary sheet).

If you haven't created a named range before it is easy. Just select the range of cells, then type the range name in the Name box above cell A1.

The macro will automatically create a worksheet containing the rows extracted from the Summary sheet. It will also prompt you for the Summary sheet column that contains the dates. Also, this macro assumes that row 1 of the Summary sheet contains a header that you want copied to the extracted data sheet.

Here's the code:

Code: 
Sub FilterSummaryDates()
   'Extracts date rows from Summary sheet and places them on new sheet
   Dim iRow    As Long
   Dim dCol    As Variant     'Date column specifier
   Dim Drng    As Range       'Range containing filter dates
   Dim dRow    As Integer     'row within FilterDates range
   Dim ShNum   As Integer
   Dim ShName  As String
   Dim ShRow   As Long        'row on destination sheet
   
   Set Drng = Range("FilterDates")
   
   dCol = InputBox("Enter Summary sheet column containing dates to be filtered" & vbLf & _
                   "Can be either number of letters (e.g., ""B"" or ""2"", without quotes)", _
                   "Identify column", "A")
                   
   If IsNumeric(dCol) Then dCol = CInt(dCol)
   
   'create new sheet
   ShNum = 1
   ShName = "Extracted Dates(1)"
   Do While SheetExists(ShName)
      ShNum = ShNum + 1
      ShName = "Extracted Dates(" & ShNum & ")"
   Loop
   Worksheets.Add after:=Worksheets("Summary")
   ActiveSheet.Name = ShName
   ShRow = 2      'Start with row 2 to bypass header row
      
   With Worksheets("Summary")
      'Copy header row of Summary sheet to new (destination) sheet
      .Rows(1).Copy Destination:=Rows(1)

      For iRow = .Cells(65536, dCol).End(xlUp).Row To 2 Step -1
      
         For dRow = 1 To Drng.Rows.Count
            If Drng(dRow, 1) = .Cells(iRow, dCol) And Not IsEmpty(Drng(dRow, 1)) Then
            '  extraction date found
               .Rows(iRow).Copy Destination:=Rows(ShRow)
               .Rows(iRow).Delete
               ShRow = ShRow + 1
               Exit For
            End If
         Next dRow
         
      Next iRow
   End With
   
   'sort Extracted data by date
   Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Sort Cells(1, dCol), xlAscending, header:=xlYes
   
End Sub

Function SheetExists(ShName As String) As Boolean
   On Error GoTo NoSuchSheet
   Set sh = Sheets(ShName)
   SheetExists = True
   Exit Function
NoSuchSheet:
   SheetExists = False
End Function

You must include the SheetExists function when you paste this into your macro module.

Keep Excelling.

Damon
 
Last edited:
Upvote 0
Hi Damon

I finally got a chance to try the code and I get a compile error on this piece:
Function SheetExists(ShName As String) As Boolean
On Error GoTo NoSuchSheet
Set sh = Sheets(ShName)
SheetExists = True
Exit Function
NoSuchSheet:
SheetExists = False
End FunctionYou must include the SheetExists function when you paste this into your macro module

I copied the entire code into a module. I created a sheet I named Dates and typed in some dates and named the range FilterDates. I am sure it is something I am doing wrong--almost there!!

Jam
 
Upvote 0
Hi Damon

Ignore last reply. I got it to work but my dates are not appearing because my summary sheet column headings are on row 4, column b. My dates are on row 6, column c. I do not feel confident enough to fool with the code. I am assuming once it works it extracts the entire row of data (meaning date, name, rate, hourly rate)?

Jam
 
Upvote 0
Hi Damon

You have been a lifesaver. My summary sheet has a glitch and I wondering if you could look at the code and help me fix it. I have two sheets I want to appear on the summary. I have several sheets named TS1, TS2, TS3, etc. They appear on the summary sheet perfectly lined up with the hyperlink. When I include a different sheet name (CTS1) the data appears but drops a row and then it is not lined up correctly with the hyperlink. When I include another sheet (CTS2) it drops a line and then the data for CTS1 disappears. I played around with this piece of the code but I am at a roadblock. Is this something you can help me with? Here is the summary sheet. Thought it might be better to fix summary code versus extracted dates code!!

<TABLE style="WIDTH: 375pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=499><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 127pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=169 colSpan=2>Cost Summary


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=138></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>Worksheets</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Hourly Rate</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Hours</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>TS1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 align=right>5/26/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">DEPUTY DOG</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$79.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$260.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>TS2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 align=right>5/30/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">HUEY DUCK</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$100.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$335.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>TS3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 align=right>5/30/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">RICHIE RICH</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$112.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$314.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>CTS1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>CTS2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 align=right>5/30/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 align=right>$204.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>
Here is the code:
Sub GrabData()
'
' GrabData Macro
' Macro recorded 2/12/2009
' Copy all data from every sheet to current sheet
'
Dim wsA As Worksheet
Dim wsANm As String
Dim ws As Worksheet
Dim r As Integer
'
Set wsA = Worksheets.Add(Before:=Worksheets(1))
wsANm = wsA.Name
On Error Resume Next
wsA.Name = "Merged Data"
NameErr: If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("Merged Data").Delete
Application.DisplayAlerts = True
wsA.Name = "Merged Data"
End If
If wsA.Name = wsANm Then GoTo NameErr
On Error GoTo 0
'
For Each ws In Worksheets
If ws.Index <> wsA.Index And ws.Name <> "" Then
r = wsA.Range("A65536").End(xlUp).Row + 1
ws.UsedRange.Copy Destination:=wsA.Cells(r, 1)
End If
Next ws
wsA.Range("A1").Select
'
End Sub

Sub SummarySheet()
'
' SummarySheet Macro
' Macro recorded 2/12/2009
' Create links to all sheets and show totals
'
Dim ws As Worksheet
Dim wsANm As String
Dim wsA As Worksheet
Dim r As Integer
Dim MyTot1 As Variant
Dim MyTot2 As Variant
Dim MyTot3 As Variant
Dim MyTot4 As Variant
Dim MyTot5 As Variant
Dim MyTot6 As Variant
Dim MyTot7 As Variant
Dim MyTot8 As Variant
'
' Cell that has the totals on each sheet
Set MyTot1 = Range("c7")
Set MyTot2 = Range("c6")
Set MyTot3 = Range("C11")
Set MyTot4 = Range("e11")
Set MyTot5 = Range("g11")
Set MyTot6 = Range("C2")
Set MyTot7 = Range("F33")
Set MyTot8 = Range("H33")
'
Set wsA = Worksheets.Add(Before:=Worksheets(1))
MyTot1 = MyTot1.Address
MyTot2 = MyTot2.Address
MyTot3 = MyTot3.Address
MyTot4 = MyTot4.Address
MyTot5 = MyTot5.Address
MyTot6 = MyTot6.Address
MyTot7 = MyTot7.Address
MyTot8 = MyTot8.Address
wsANm = wsA.Name
On Error Resume Next
wsA.Name = "Summary"
NoName: If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("Summary").Delete
Application.DisplayAlerts = True
wsA.Name = "Summary"
End If
If wsA.Name = wsANm Then GoTo NoName
On Error GoTo 0
'
r = wsA.Range("B65536").End(xlUp).Row + 1
wsA.Cells(r, 2).Value = "Cost Summary"
wsA.Cells(r, 2).Font.Bold = True
wsA.Cells(r + 2, 2).Value = "Worksheets"
wsA.Cells(r + 2, 2).Font.Italic = True
wsA.Cells(r + 2, 3).Value = "Date"
wsA.Cells(r + 2, 3).Font.Italic = True
wsA.Cells(r + 2, 4).Value = "Name"
wsA.Cells(r + 2, 4).Font.Italic = True
wsA.Cells(r + 2, 5).Value = "Hourly Rate"
wsA.Cells(r + 2, 5).Font.Italic = True
wsA.Cells(r + 2, 6).Value = "Hours"
wsA.Cells(r + 2, 6).Font.Italic = True
wsA.Cells(r + 2, 7).Value = "Total"
wsA.Cells(r + 2, 7).Font.Italic = True
For Each ws In Worksheets
If ws.Index <> wsA.Index And ws.Name <> "Merged Data" And ws.Name <> "Stats" And ws.Name <> "BlankTS" And ws.Name <> "BlankChkpt" And ws.Name <> "DATES" And ws.Name <> "Extracted Dates(1)" And ws.Name <> "Cover" Then
r = Range("B65536").End(xlUp).Row
wsA.Hyperlinks.Add Anchor:=wsA.Cells(r + 0, 1), Address:=""
wsA.Hyperlinks.Add Anchor:=wsA.Cells(r + 1, 2), Address:="", _
SubAddress:=ws.Name & "!a1", TextToDisplay:=ws.Name
wsA.Cells(r + 1, 3).Value = ws.Range(MyTot1).Value
wsA.Cells(r + 1, 4).Value = ws.Range(MyTot2).Value
wsA.Cells(r + 1, 5).Value = ws.Range(MyTot3).Value
wsA.Cells(r + 1, 6).Value = ws.Range(MyTot4).Value
wsA.Cells(r + 1, 7).Value = ws.Range(MyTot5).Value
wsA.Cells(r + 2, 3).Value = ws.Range(MyTot6).Value
wsA.Cells(r + 2, 6).Value = ws.Range(MyTot7).Value
wsA.Cells(r + 2, 7).Value = ws.Range(MyTot8).Value
End If
Next ws
'

JAM
 
Upvote 0
Hi Damon

I have been traveling and I do not get enough sleep--what an idiot I am!! The extracting dates works perfectly. If you could help me with the summary sheet I would be very grateful.

JAM
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Automate rows to disappear?
Replies
2
Views
97
Peter_SSs
Peter_SSs
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
210
Tetra201
Tetra201
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
203
jolivanes
jolivanes
A
  • Solved
VBA code to extract a table
Replies
5
Views
282
Anthony47
A
D
  • Question Question
Macro Insert column with date of today
Replies
1
Views
68
MikeVol
MikeVol

Forum statistics

Threads
1,224,548
Messages
6,179,451
Members
452,915
Latest member
hannnahheileen

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