searching for more current dates in Excel (urgent)

[21MSU]

I am trying to bulid a macro to update a spreadsheet. The macro should open up a workbook, select the sheet and find a date that is higher (more current) than the last date on the main page. For instance

UnitConditons1.xls
Date VIS COLOR API SULFUR
2/18/2003 1:00:00 AM 20 3 40 3
2/19/2003 12:30:00 PM 25 3 41 4

C-60.xls (Sheet1)

2/20/2003 11:00:00 AM 30 5 32 3
2/18/2003 1:00:00 AM 20 4 33 3.5

C-60.xls (Sheet2)
2/21/2003 1:00:00 AM 23 4 32 3
2/17/2003 12:00:00 AM 20 3 35 4

In this example, the macro should take the last date on UnitConditions1.xls (2/19/2003 12:30:00 PM) and then open up C60.xls (Sheet2)and go through the dates to find a date(s) greater (more current) and paste that date and respective data on the UnitConditions1.xls page right below the 2/19/2003 12:30:00 PM in chronological order. Then it should go to C60.xls (Sheet1) find a more current date and paste it on UnitCondition1.xls. If there are no more current dates then the nothing gets pasted.

Is there any simple way to build a macro to accomplish this? Please help me out!
Thanks!

 

[Zathras]

I don't think there is a simple way, but here is one way:

Option Explicit
Const MAIN_BOOK_NAME = "UnitConditions1.xls"
Const MAIN_SHEET_NAME = "UnitConditions1"
Const CSIXTY_BOOK_NAME = "C:\C-60.xls"
Const CSIXTY_SHEET1_NAME = "Sheet1"
Const CSIXTY_SHEET2_NAME = "Sheet2"
Const COL_DATE = 1
Const COLUMNS_TO_COPY = 5
Const SORT_CELL = "A2"
Const FIRST_DATA_ROW = 2

Sub Update()
Dim MainBook As Workbook
Dim C60Book As Workbook
Dim MainSheet As Worksheet
Dim LastEntry As Range
Dim LastDate As Date
Dim NewData As Range
Dim nStartRow As Long
 
  ' Open "C60" workbook
  Set MainBook = Workbooks(MAIN_BOOK_NAME)
  Set MainSheet = MainBook.Sheets(MAIN_SHEET_NAME)
  On Error Resume Next
  Application.Workbooks.Open (CSIXTY_BOOK_NAME)
  On Error GoTo 0
  Set C60Book = ActiveWorkbook
  If ActiveWorkbook Is MainBook Then
    MsgBox "Unable to open " + CSIXTY_BOOK_NAME + " -- Job cancelled"
    Exit Sub
  End If

  ' Get last date from main sheet
  Set LastEntry = MainSheet.Cells(65536, COL_DATE).End(xlUp)
  LastDate = LastEntry.Value

  'Get entries from "Sheet2"
  nStartRow = FIRST_DATA_ROW
  While GetNewData(C60Book, CSIXTY_SHEET2_NAME, _
                           nStartRow, LastDate, NewData)
    If Not NewData Is Nothing Then
      Set LastEntry = LastEntry.Offset(1, 0)
      NewData.Copy Destination:=LastEntry
    End If
  Wend

  'Get entries from Sheet1"
  nStartRow = FIRST_DATA_ROW
  While GetNewData(C60Book, CSIXTY_SHEET1_NAME, _
                          nStartRow, LastDate, NewData)
    If Not NewData Is Nothing Then
      Set LastEntry = LastEntry.Offset(1, 0)
      NewData.Copy Destination:=LastEntry
    End If
  Wend
  
  'Sort and finish
  MainSheet.Activate
  MainSheet.Range(SORT_CELL).Sort Key1:=Range(SORT_CELL), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  ActiveWorkbook.Save
  
Set LastEntry = Nothing
Set NewData = Nothing
Set MainBook = Nothing
Set MainSheet = Nothing
Set C60Book = Nothing
End Sub

Function GetNewData(Book As Workbook, SheetName As String, StartRow As Long, _
             ReferenceDate As Date, NewerData As Range) As Boolean
Dim wsh As Worksheet
Dim nLastRow As Long
Dim Dates As Range
Dim c As Range
Dim bFound As Boolean

  bFound = False
  Set wsh = Book.Worksheets(SheetName)
  nLastRow = wsh.Cells(65536, COL_DATE).End(xlUp).Row
  Set Dates = Range(wsh.Cells(StartRow, COL_DATE), wsh.Cells(nLastRow, COL_DATE))
  If StartRow <= nLastRow Then
    For Each c In Dates
      On Error Resume Next
      If c.Value > ReferenceDate Then
        bFound = True
        Set NewerData = Range(c.Cells(1, 1), c.Cells(1, COLUMNS_TO_COPY))
        StartRow = c.Row + 1
        Exit For
      End If
      On Error GoTo 0
    Next c
  End If
  GetNewData = bFound
  Set wsh = Nothing
End Function

 

[21MSU]

Thanks! I am getting errors on

Set MainBook = Workbooks(MAIN_BOOK_NAME)
Set MainSheet = MainBook.Sheets(MAIN_SHEET_NAME)

Set LastEntry = MainSheet.Cells(65536, COL_DATE).End(xlUp)

What will fix these? (I am new to VBA)
I would like the macro to find the smallest date on each sheet in C-60.xls greater than the last date in >UnitConditions1.xls, but the dates will have to be placed on UnitConditions1.xls in chronological order. Sometimes C60.xls (sheet2) might be updated 5 times and C60.xls might only be updated 2 times. Therefore the macro needs to find the smallest date > last date on UnitCondition.xls and paste it on UnitConditions. In addition to that it needs to grab the data associated with this date. i.e.

UnitConditons1.xls (Main Page)
>
>2/18/2003 1:00:00 AM
>2/19/2003 12:30:00 PM
>
>C-60.xls (Sheet1) E F G J K L
>
>2/20/2003 11:00:00 AM 30 20 13 20 20 23
>2/18/2003 1:00:00 AM 20 30 20 20 24 34
>
>C-60.xls (Sheet2) E F G H K
>2/21/2003 1:00:00 AM 20 23 34 57 3
>2/17/2003 12:00:00 AM 20 38 39 39 4

In this instance, C60.xls has been updated twice (sheet1nce, and sheet2nce). The macro should search through sheet1 and find 2/20/2003 11:00:00 AM paste that on UnitConditions then paste its respective data from columns (E,F,G,J,K,L) to UnitConditions in columns (H,I,J,K,L,M). Then it should find 2/21/2003 1:00:00 AM on sheet 2 paste it on UnitConditions and then paste its respective data from columns (E,F,G,H,K) to UnitConditions page in columns (C,D,E,F,G).
Note after every pasted date on the UnitConditions page Range("N5:BQ5&quot needs to be selected, copied and pasted(formulas then values) on that same updated line.

(UnitConditions1.xls (only pay attention to Sheet(C60);C60.xls (only Sheets("C-624, E-602&quot will be updated) in both on these sheets column A contains the date (in a formula format,therefore only a value needs to be pasted on UnitConditions)

also, on Sheet "C-624" only the data in columns (E,F,G,J,K,L) need to be copied and pasted (these are just values).

NOTE: when the data from this sheet are copied they have to be pasted on UnitConditions in columns (H,I,J,K,L,M).

also, on Sheet "E-602" only the data in columns (E,F,G,H,K) need to be copied and pasted (these are just values).

NOTE: when the data from this sheet are copied they have to be pasted on UnitConditions in columns (C,D,E,F,G) .

On UnitConditions the data (they are actually formulas) in Range("N5:BQ5&quot need to be copied and pasted on the same line(s) that were updated. The formulas need to be copied and then the values( or however, I only want the updated values).

 

[Zathras]

The first problem can probably be fixed by setting the correct names in the constants. You didn't provide complete information, so I had to guess a little about sheet names and which columns had the data you are working with.

As for the rest, the code works according to the original post. The modifications should not be that difficult, but I don't have the time to do it for you. Sorry.

Consider it a good opportunity to become fluent in VBA.