Copy row into different workbk IF dates match

Heidist - Nov 22, 2010 at 07:16 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 27, 2010 at 02:42 AM

I have an excel question, I'd like to learn more about excel, but not sure where to start, I know it can do MUCH more than I am aware of. I know there is back coding that can be done... where would I start to learn this?

My specific question is this:

I have an excel spreadsheet with about 15 columns (SIN, Date, First Name, Last Name, Birthdate, etc) that is a list of students with their school name, start date and end date.

I have a different spreadsheet that has a separate tab for each month.

I would like a formula or macro or coding(and how to use it) that would take the date column from spreadsheet 1, and if the date is in the month of September, it will copy 4 of the columns from that row into spreadsheet 2 into tab "September". and the same if the date is in October it would copy into tab "October" in spreadsheet 2, and on and on.

I think it's amazing that all of you guys/gals help out all of us who don't know! Thank you for your help!


7 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 23, 2010 at 05:20 AM
What is the level of your knowledge of excel. Have you heard of "macros". Do you know where to park the macros. Etc . can you modify a macro to suit you.
1. In the beginning spend some time to design the workbook
2. Better do not have different workhbooks (files) for each month but different sheets in the same workbook for one problem as far as possible.
3. The first sheet you call "data". How? Double click the sheet tab(sheet name at the bottom) and type data.
4. Change the names of other sheets like sep, oct ,nov. etc.(if necessary insert more sheets)
5. Enter you data in sheet called "data"
6. The sample data in sheet data is (from row 1 column 1
sin date name
a 10/9/1997 john
s 11/8/1998 williams
d 10/5/1997 richard
f 11/25/1998 collins
7. You can do one thing click you can RECORD a macro taking the steps you need
8. How?
9. Click tools-macro-record new macro
10. Leave the macro name as it is but remember
11. Take steps e.g. highlight a2,b2,c2 and hit control+C and go to that particular month (Oct) sheet and select top empty blank cell and hit control+V
12. Do the same for a3,b3,c3 and copy it in the corresponding sheet (Nov)
13. Continue for all the rows.
14. Now click to tools-macro=stop recording.
16. Now click tools-macro-macros. You will see the macro name which you have RECORDED.
17. Highlight the particular macro and click run(on the right side)
18. The macro is run.
You must buy first a book on excel of the version you have and do all the exercises. Then for writing a macro(not recording macro) you need another book on "excel programming" for your version of excel.

Slowly learn by and by you will become an expert. Whenever you have doubts post your questions I n the newsgroup giving sample data or code and explain what is the problem and what you want.
Wish you best efforts to your learning curve.
Venkat, I appreciate your help, unfortunately I don't think this is what I'm looking for.

I do have a fairly good knowledge base on Excel, but nothing on VBA, and I think VBA is what I'm going to need, there needs to be a loop (to repeat a similar action on different lines) but not over-right lines already there, it needs to take only NEW lines and copy them over. It also needs to assess the date in each new line and copy it over to the appropriate tab (the corresponding month's tab).

Yes I only have 2 spreadsheets, the main spreadsheet with all the data in one tab, and the other spreadsheet with a tab for each month of the year. I would like the VBA/Macro to seach the data tab for any new entries (SIN's) against the appropriate month in the other spreadsheet, if it's not there to copy it over, if it's already there, skip to the next.

clear as mud?
I'd like to add:

The Main spreadsheet is a list of students, it is updated many times a day with new students, I am looking for a macro that when the second spreadsheet (2010 spreadsheet with tabs for each month) is opened you can run a macro that will search the main spreadsheet for SIN numbers that are not on spreadsheet 2, and add them to the appropriate tab in spreadsheet 2 (2010 spreadsheet with tabs for each month).

For example, If I open the main spreadsheet and add this student
SIN FN LS Start End School Entered
111 222 333 Johnny Cash 09/01/2010 04/30/2011 GUAG 10/15/2010

when I save this new data and I open the 2nd spreadsheet (2010 spreadsheet with tabs for each month) and I run the macro it should see that the entered date is October, therefore it should pull this student's SIN, FirstName and LastName and plug it into the first empty cell (below all other copied data from previously running this macro) into the tab named "October".

I hope this helps clarify what I'm trying to do!
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 23, 2010 at 08:24 PM
can you please upload in a small extract of main sheet and one or two monthly sheets. then explain with reference to this data what you would like to do. post the web page where the file is uploaded. no password.

There should be some logical way of finding out of the new entries.


the main spreadsheet is updated with new students several times everyday. I am looking for coding that when the 2010 monthly spreadsheet is open a macro can run that will look at the main spreadsheet, find any new students (SIN's) and place them into the 2010 monthly spreadsheet on the correct tab based on the received date month.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 24, 2010 at 08:01 PM
you have not indicated how to find the new entries.

1. Of course every time you can delete data from original monthly sheet of example in september sheet of second workbook. this has to be done for all the sheets whether new entries are made or not . the macro will unnecessary take more time.

2.It would be better iif there is some indication of the new entries (the old entries are already copied in the monthly sheets. one trick you can use leave a blank row between set of entries. this has to start even in the first coping that is when you first enter leave row 2 blank and then enter from row 3.(enter as many rows as you have data).

3. perhaps 2 is not possible as the new data is automatically entered in which case you have to go to no. 1

4. instead of when opening the worksheet (or do you mean workbook) I suggest you close the file after entering the new data and saving it.
that mean even code is "before closing". this can be done

In you nomenclature you must keep this in mind
workbooks- a workbook has many worksheets
worksheet- you mean a particular worksheet(s) in a workbook.
in your case there are two workbooks(or files) one having main data (only one sheet) and the other workbook having monthly sheets. (one sheet for each month). keep these in mind

on receipt of reply I shall start writing the macro (give item by item reply)
Dear venkat,

I'm sorry if I'm not being clear enough, thank you for sticking with me on this. Let me try again.

I was hoping that when I open the workbook 2010 monthly there could be a macro that would compare the SIN's in each tab and compare those against the SIN's in the main workbook. If there are SIN's with "received date" of between 2010/09/01 and 2010/09/30 that are not on the "September" tab of the 2010 monthly workbook then they should be added to the bottom of the list in the first empty row.

It won't work if the macro erases what's already in the September tab and creates a new list because data will be added (their withdrawal date and scholarship amounts) to other columns for each SIN, if the macro erases everything and inputs it again the data might not line up with the correct SIN's anymore and that would be a HUGE mess.

It doesn't matter to me where the macros is stored (in which workbook) or what the trigger is that starts it (opening the 2010 monthly workbook, closing the main workbook, saving the main workbook, etc) just so long as the macro looks at the list of SIN's in the main workbook, depending on the date in the "received date" column (lets say its September) it looks for that SIN in the September tab of the 2010 monthly workbook, if it's already there it skips it and moves onto the next. If the next received date is October for example, then the macro should search the October tab of the 2010 monthly workbook to see if that SIN is already there, if it's not it should add it to the bottom of the list, in the first empty row (along with the first name, last name and tuition amount).

In summary:
#1 - no I don't want it to delete the data already there, that won't work for us.
#2 - I suppose we might be able to put spaces between each set of SIN's for each time we run the macro. but then would we delete the empty row from the last time, how will the macro know which empty row to start at?
#3 - I'm not sure what you mean, but no i don't like option #1
#4 - whatever is easier is fine by me

I hope I answered everything!

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 25, 2010 at 09:53 PM
do not worry about need for repeated classifications required and sent. Language is a poor substitute of what we want co communicate. That is why sometimes it is better to give examples than elaborate verbal texts.

I am sending back your files .

you can download form the following

what I did:
1. your files are xlsx files that is not macro enabled.
2. so I saved both the files as macro enabled -how ? if you do not know
a. click main ribbon at the top left of each workbook
b. take cursor to "save as" and then take cursor to excel macro enable workbook ( second in the list) and click
c. choose the folder and the name (As I am using the name in the macro the name of the workbook should be small so that when I use in the macro there is no spelling mistake even no unnecessary spaces etc.
d. I have called the workbooks "heidist(space)main.xlsm" and
I suggest you keep the names in that case you need not make any modifications in the macro

now in the "main" workbook I have introduced blank rows in row no. 7 and 10.
this means that rows 2 to 6 are entered (downloaded) in the first day which is already gone in months workbook
row 7 to 9 entered on second day already supposed to be copied in months workbook.
rows 11 and 12 are the LATEST ENTRIES.

When you run the macro these latest entries(and only relevant columns) will be copied in the "months" workbook in the appropriate month's sheets.
if you go through the code statements you can understand the logic.

once the macro is run you check and save the "months" workbook

I have parked the macro in the "main" file. Even though the file can be parked in either of these files you better keep it in main file. you see the months file after running the macro

if you change the file name instead of typing in the macro go to the file name in the folder and right click and click "rename". the file name is highlighted. you copy this(control+C) and paste it is the relevant code statements (control+V) that is
set wb1=............
set wb2=.............................


Sub test()
Dim r As Range, wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet
Dim mmonth As String, c As Range
Set wb1 = Workbooks("heidist main.xlsm")
With wb1
Set sh1 = .Worksheets("Sheet1")
End With
Set wb2 = Workbooks("heidist months.xlsm")
With sh1
Set r = .Cells(Rows.Count, "E").End(xlUp)
Set r = r.End(xlUp)
Set r = Range(r, r.End(xlDown))
MsgBox r.Address
For Each c In r
mmonth = Format(c, "mmmm")
MsgBox mmonth
Range(.Cells(c.Row, "b"), .Cells(c.Row, "g")).Copy
    With wb2
    With .Worksheets(mmonth)
    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    End With
    End With
    Next c
    End With

End Sub

you asked a question how to find the latest blank row (or latest entries by introducing the blank row). what the macro does is:
1.find out the last row in the data sheet(by going to the very last row and going up). I am interested in column E only
2 now form this last E I made a end(xlup) and it will go to the first row after the last blank row.
3. now we are interested the cells between this E and last E and then slight further maneuvering.

If are confident you are at liberty to modify the macro.

At present I am not giving an "undo" sub and it needs little tricky macro. shall think about it. After all this do you get what you want????????????? check and let me know.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 25, 2010 at 09:55 PM
see whether macro does what you want. Once it is ok I shall tell you how to run this macro automatically when you close and save the files.
I don't have a premium account, so it won't let me download, only upload. Could you send me the code here?
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 27, 2010 at 02:42 AM
I seem to miss another message one before my last message. does that not contain the macro. Premim account with is not necessary. try again.

my own compuer is out of order. after it becomes ok I shall again send you the macro.