Simple question if you know how...

[wils_152]

Ok. Very new to VBA and to a lesser extent, Excel. My first post! Apologies if this is rusty.

My question: I have a single workbook with two worksheets. Worksheet 2 is a "permanent" list of recent actions on a file, and worksheet 1 basically updates worksheet 2 with any new entries that don't already exist in worksheet 2 (it's basically recording the results of a report which is run once a week).

I'm literally dipping my toes in VBA for the first time and I don't know how to get the new info from worksheet 1 into worksheet 2 (mechanics-wise, I'm comparing the latest report date in w/sheet 2 to the date(s) in w/sheet 1, and where the dates in w/sheet 1 are larger, I need to copy and paste those rows into w/sheet 2).

I'm getting annoyed because it's very simple in my head (insert smart comment here), but my ignorance of VBA is shoing itself.

:P

 

[mumps]

What are the names of the 2 sheets? In which columns in the 2 sheets are the dates located?

 

[wils_152]

What are the names of the 2 sheets? In which columns in the 2 sheets are the dates located?

"Log New" and "Log History"

Dates are in column B in both.

 

[mumps]

I am assuming that both sheets have headers in the first row and the data starts in row 2. Try this macro:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Log New").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lDate As Date
    Sheets("Log History").Select
    lDate = WorksheetFunction.Max(Range("B:B"))
    Sheets("Log New").Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:=">" & lDate
    Sheets("Log New").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Log History").Cells(Sheets("Log History").Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Log New").AutoFilterMode = True Then Sheets("Log New").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[wils_152]

I am assuming that both sheets have headers in the first row and the data starts in row 2. Try this macro:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Log New").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lDate As Date
    Sheets("Log History").Select
    lDate = WorksheetFunction.Max(Range("B:B"))
    Sheets("Log New").Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:=">" & lDate
    Sheets("Log New").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Log History").Cells(Sheets("Log History").Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Log New").AutoFilterMode = True Then Sheets("Log New").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Many thanks - I'll give that a go when I get a chance.

 

[mumps]

My pleasure.

 

[wils_152]

My pleasure.

Aaarghh it doesn't work***. I think I've got an issue with the way the dates are formatted in the file I'm working on. It's a third party file, where for example the 29th June 2017 would be shown as: 20,170,629 - why the date has been set up like that, I don't know. And even that could be a red herring.

Feeling quite frustrated at this point so I'm going to walk away, calm down and come back to this tomorrow. Can you let me know what each line is doing, as I basically have no frame of reference for determining what the script is actually doing.


*** when run, the lines on the "log new" worksheet are minimised, but I then get a 1004 run-time error saying "No cells were found" at the following line:

Sheets("Log New").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Log History").Cells(Sheets("Log History").Rows.Count, "A").End(xlUp).Offset(1, 0)

 

[mumps]

Here is the macro with explanatory comments. Both columns B have to be formatted as an 'Excel' date otherwise the macro won't find any cells that meet the filter criteria. That's why you get the error "No cells found".

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Log New").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lDate As Date
    Sheets("Log History").Select
    lDate = WorksheetFunction.Max(Range("B:B")) 'finds the most recent date in column B of 'Log History'
    Sheets("Log New").Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:=">" & lDate 'filters 'Log New' to show only the rows with dates after the most recent date in 'Log History'
    Sheets("Log New").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Log History").Cells(Sheets("Log History").Rows.Count, "A").End(xlUp).Offset(1, 0)
            'the line above copies the filtered visible rows from 'New Log' to 'Log History'
    If Sheets("Log New").AutoFilterMode = True Then Sheets("Log New").AutoFilterMode = False 'removes the filter
    Application.ScreenUpdating = True
End Sub

 

[wils_152]

Makes sense! I think the date was screwing things up - I've changed LastRow to "Long" rather than Date, and it seems to be working fine.

Many many thanks for helping with this, I must admit the solution was completely different to how I thought it would look (I was thinking get the largest date in the log history sheet, then loop through the rows in the log new sheet and copy across the ones that are larger).

Just as importantly, this has given me a "foothold" into VBA.

Excellent - thanks for the help!

 

[mumps]

You are very welcome. I used filtering because it is much faster than looping, especially if you have a very large data set.