Copy row into different workbk IF dates matchClosed

Question

Hello, 

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!

Configuration: Windows XP / Firefox 3.6.12

venkat1926 · Answer

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.
15.	NOW DELETE ALL DATA FROM MONTHLY SHEETS (AND NOT SHEET DATA)
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.

Heidist · Answer

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 · Answer

can you please upload in speedyshare.com 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.

venkat1926 · Answer

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)

venkat1926 · Answer

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 

http://www.speedyshare.com/files/25383879/heidist_Main.xlsm

https://authentification.site/files/25383880/heidist_months.xlsm

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 
      "heidist(space)months.xlsm"
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 
READ THIS CAREFULLY:
EVERY TIME IF YOU CHECK(REPEAT CHECK) AND SAVE MONTHS WORKBOOK TAKE A BACK UP COPY IN ANOTHER FOLDER SO THAT IN THE ATTEMPT TO COPY NEXT NEW ENTRIES IF THERE IS MESS UP YOU CAN RETRIEVE THE DATA. BE CAREFUL ON THIS. DO NOT TAKE BACK UP IF YOU HAVE NOT CHECKED. 

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 
BOTH WORKBOOKS SHOULD BE KEPT OPEN 

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=.............................

THE MACRO IS 

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 · Answer

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.

venkat1926 · Answer

I seem to miss another message one before my last message. does that not contain the macro. Premim account with speedyshare.com is not necessary. try again. 

my own compuer is out of order. after it becomes ok I shall again send you the macro.

Copy row into different workbk IF dates match

7 responses

Similar discussions

Subscribe To Our Newsletter!