Simple question if you know how...

wils_152

New Member
Joined
Jan 23, 2018
Messages
5
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
What are the names of the 2 sheets? In which columns in the 2 sheets are the dates located?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
I am assuming that both sheets have headers in the first row and the data starts in row 2. Try this macro:
Code:
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

New Member
Joined
Jan 23, 2018
Messages
5

ADVERTISEMENT

I am assuming that both sheets have headers in the first row and the data starts in row 2. Try this macro:
Code:
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.
 

wils_152

New Member
Joined
Jan 23, 2018
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
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".
Code:
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

New Member
Joined
Jan 23, 2018
Messages
5
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
You are very welcome. :) I used filtering because it is much faster than looping, especially if you have a very large data set.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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
Top