Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
I have two worksheets. One is called Calendar and the other is called Ghost.
Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
when information is added to Ghost the code
This is too ambiguous to work from. What infromation? What do you want to specifically trigger the macro to copy data in column F of Ghost to the cell below the corresponding date in Calendar? Are both the date in column C and the data in column F of Ghost entered by the user, or is one of these columns preset? I was half way through the macro when it dawned on me that either or both columns in Ghost might be changed and there is not way of knowing which would occur first, therefor, if a worksheet change event was used to execute the macro, it might copy a blank cell if the wrong column had data entered first. If only one column will have an entry made, then we can make a pretty simple macro to fix the problem. "AUCP03" wrote in message ... I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
The "information" copied is in Col A - Col C. Col A is a list of item
numbers. Col B is a list of names. Col C is the dates. This information is automatically populated from other sheets or at least will be copied from them in the Col A-C format. I do a little reformatting of the information input into Ghost to get Col F [=E3&" - "&A3]. Column E is initials determined via the formula =INDEX(K$4:K$15,MATCH(B3,{"Chapman","Cobb","Darby" ,"Davis","Gooding","Hunt","Phillips","Quick","Ray" ,"Richey","Spear","Wade"},0)) So I am only using Col C and F from ghost but there are other things going on. Once entered in the sheet they will not be changed unless there is a user entry error in which case care would be taken to correct. And it will be possible for multiple entrees to occur on the same day. Ghost will be a hidden sheet eventually; I am using it as a go between. Im not sure what the best method for starting the macro would be. I dont want to duplicate the information on the Calendar, so I guess a button on the Calendar or an execute upon exit cell command? I hope that didn't complicate things too much. Thanks for the response. "JLGWhiz" wrote: when information is added to Ghost the code This is too ambiguous to work from. What infromation? What do you want to specifically trigger the macro to copy data in column F of Ghost to the cell below the corresponding date in Calendar? Are both the date in column C and the data in column F of Ghost entered by the user, or is one of these columns preset? I was half way through the macro when it dawned on me that either or both columns in Ghost might be changed and there is not way of knowing which would occur first, therefor, if a worksheet change event was used to execute the macro, it might copy a blank cell if the wrong column had data entered first. If only one column will have an entry made, then we can make a pretty simple macro to fix the problem. "AUCP03" wrote in message ... I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
Here is some info on Index/Match:
http://www.contextures.com/xlFunctions03.html Here is some VBA code doing essentially the same thing as that from above: http://en.allexperts.com/q/Excel-105...code-using.htm This will give you the first empty cell underneath the date on Calendar. Sheets("Calendar").Select Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste This assumes the date is in Column A; it probably isn't in column A, so just change it to suit your needs. As for the rest, too vague for me to know what to do for sure. Maybe you can post back with some more details... HTH Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AUCP03" wrote: I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
First thank you for the response. Now I will try to clarify and I'm sorry
about the length of my response I have a workbook with three spreadsheets that is getting to large to manage and I would like to automate it as much as possible. I have been tinkering with this for a while with no success. I am not a very good programmer, so any response please be gentle. Worksheet #1 (Called Tracker) This worksheet has data entered in a list (Excel 2003). The headers are in row 2 and the data starts in row 3 column A. I am only interested in the data in the first three columns A, B, & C. Column A Data These cells are numbers Column B Data These cells are peoples names Column C Data These cells are dates (mm/dd/yyyy) Worksheet #2 (Called Calendar) This worksheet is set up as a calendar and ultimately where I want to populate the data from Tracker. The dates are in columns B-H and J-P (Sun-Sat with a space column in the middle). They are not in any particular row, but they start in row #4. After row #4 the dates appear in no particular order with respect to what row they are in next. The dates do whoever appear in a rows that span both weeks. Worksheet #3 (Called Ghost) This sheet I copied the first 900 rows of Col A-C of Tracker and pasted it in A3. This makes it so as the Tracker sheet has new rows added the Ghost sheet is €śupdated€ť, I picked 900 because I wont ever exceed that many row entrees in a single year. On this sheet I manipulate some of the information in columns in A-C. Column E (Initials) This column changes the value in column B to initials established in K$4:K$15 there are 13 names in all. (hence the Index instead of IF statements) =INDEX(K$4:K$15,MATCH(B3,{"Chapman","Cobb","Darby" ,"Davis","Gooding","Hunt","Phillips","Quick","Ray" ,"Richey","Spear","Wade"},0)) Initials K$4:K$15 is BA BB RC DD ME TF KG LH RI EJ TK TL GM Column F (Combined Info) This column combines column A and column E following this formula =E3&" - "&A3 So the cells look like BA - 01-004-09 Now what I want to do is take data from Ghost and populate Calendar with it. I want to take the cells in column C (Date) and use that to move the cells in column F (Combined Info) of Ghost to populate the next empty cell under the cell containing the same date in Calendar. So I want to take data from Tracker to Ghost, change the data within ghost, then take the data from Ghost and move it to the corresponding date in Calendar. All this moving from Tracker to Ghost is making my file size rather large I think and I would like to automate it if possible as currently it is simply a copy paste job of cell references. I think the size is coming from the duplication of information occurring. "ryguy7272" wrote: Here is some info on Index/Match: http://www.contextures.com/xlFunctions03.html Here is some VBA code doing essentially the same thing as that from above: http://en.allexperts.com/q/Excel-105...code-using.htm This will give you the first empty cell underneath the date on Calendar. Sheets("Calendar").Select Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste This assumes the date is in Column A; it probably isn't in column A, so just change it to suit your needs. As for the rest, too vague for me to know what to do for sure. Maybe you can post back with some more details... HTH Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AUCP03" wrote: I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
Well, if I understand how your Ghost sheet is updated, you can put this macro
behind the sheet and when you update columns A - C for a given line, it will automaitcally put the value in column F of Ghost into the cell beneath the corresponding date in Calendar. To install the code, right click the name tab on Sheets("Ghost"), then click "View Code" in the drop down menu. Copy the code below and paste it into the code window. It tested OK, but if you have problems, post back. Sub Worksheet_Change(ByVal Target As Range) lr = Sheets("Ghost").Cells(Rows.Count, 3).End(xlUp).Row If Not Intersect(Range("C1:C" & lr), Target) _ Is Nothing Then Set dtRng = Sheets("Ghost").Range("C" & Target.Row) Set fRng = Sheets("Calendar").Cells _ .Find(dtRng.Value, LookIn:=xlValues) If Not fRng Is Nothing Then fRng.Offset(1, 0) = dtRng.Offset(0, 3).Value End If End If End Sub "AUCP03" wrote: The "information" copied is in Col A - Col C. Col A is a list of item numbers. Col B is a list of names. Col C is the dates. This information is automatically populated from other sheets or at least will be copied from them in the Col A-C format. I do a little reformatting of the information input into Ghost to get Col F [=E3&" - "&A3]. Column E is initials determined via the formula =INDEX(K$4:K$15,MATCH(B3,{"Chapman","Cobb","Darby" ,"Davis","Gooding","Hunt","Phillips","Quick","Ray" ,"Richey","Spear","Wade"},0)) So I am only using Col C and F from ghost but there are other things going on. Once entered in the sheet they will not be changed unless there is a user entry error in which case care would be taken to correct. And it will be possible for multiple entrees to occur on the same day. Ghost will be a hidden sheet eventually; I am using it as a go between. Im not sure what the best method for starting the macro would be. I dont want to duplicate the information on the Calendar, so I guess a button on the Calendar or an execute upon exit cell command? I hope that didn't complicate things too much. Thanks for the response. "JLGWhiz" wrote: when information is added to Ghost the code This is too ambiguous to work from. What infromation? What do you want to specifically trigger the macro to copy data in column F of Ghost to the cell below the corresponding date in Calendar? Are both the date in column C and the data in column F of Ghost entered by the user, or is one of these columns preset? I was half way through the macro when it dawned on me that either or both columns in Ghost might be changed and there is not way of knowing which would occur first, therefor, if a worksheet change event was used to execute the macro, it might copy a blank cell if the wrong column had data entered first. If only one column will have an entry made, then we can make a pretty simple macro to fix the problem. "AUCP03" wrote in message ... I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Date and Paste Data
JLGWhiz Thank you for the response. The Ghost sheet is updated when I click
save. The columns A-C are simply "=" the sheet they refer to, so when I add data to the sheet called Tracker and click save the "=" on Ghost are updated to whatever I input manually into Tracker. I wasn't very clear on that point in my response to ryguy7272. If you read my response to his post it is a good bit more detailed in what I am trying to do. I have worked with the code you provided and have had no luck at getting it to run. I do appreciate your help greatly. Thank you. "JLGWhiz" wrote: Well, if I understand how your Ghost sheet is updated, you can put this macro behind the sheet and when you update columns A - C for a given line, it will automaitcally put the value in column F of Ghost into the cell beneath the corresponding date in Calendar. To install the code, right click the name tab on Sheets("Ghost"), then click "View Code" in the drop down menu. Copy the code below and paste it into the code window. It tested OK, but if you have problems, post back. Sub Worksheet_Change(ByVal Target As Range) lr = Sheets("Ghost").Cells(Rows.Count, 3).End(xlUp).Row If Not Intersect(Range("C1:C" & lr), Target) _ Is Nothing Then Set dtRng = Sheets("Ghost").Range("C" & Target.Row) Set fRng = Sheets("Calendar").Cells _ .Find(dtRng.Value, LookIn:=xlValues) If Not fRng Is Nothing Then fRng.Offset(1, 0) = dtRng.Offset(0, 3).Value End If End If End Sub "AUCP03" wrote: The "information" copied is in Col A - Col C. Col A is a list of item numbers. Col B is a list of names. Col C is the dates. This information is automatically populated from other sheets or at least will be copied from them in the Col A-C format. I do a little reformatting of the information input into Ghost to get Col F [=E3&" - "&A3]. Column E is initials determined via the formula =INDEX(K$4:K$15,MATCH(B3,{"Chapman","Cobb","Darby" ,"Davis","Gooding","Hunt","Phillips","Quick","Ray" ,"Richey","Spear","Wade"},0)) So I am only using Col C and F from ghost but there are other things going on. Once entered in the sheet they will not be changed unless there is a user entry error in which case care would be taken to correct. And it will be possible for multiple entrees to occur on the same day. Ghost will be a hidden sheet eventually; I am using it as a go between. Im not sure what the best method for starting the macro would be. I dont want to duplicate the information on the Calendar, so I guess a button on the Calendar or an execute upon exit cell command? I hope that didn't complicate things too much. Thanks for the response. "JLGWhiz" wrote: when information is added to Ghost the code This is too ambiguous to work from. What infromation? What do you want to specifically trigger the macro to copy data in column F of Ghost to the cell below the corresponding date in Calendar? Are both the date in column C and the data in column F of Ghost entered by the user, or is one of these columns preset? I was half way through the macro when it dawned on me that either or both columns in Ghost might be changed and there is not way of knowing which would occur first, therefor, if a worksheet change event was used to execute the macro, it might copy a blank cell if the wrong column had data entered first. If only one column will have an entry made, then we can make a pretty simple macro to fix the problem. "AUCP03" wrote in message ... I have two worksheets. One is called Calendar and the other is called Ghost. Calendar is a sheet with dates in Columns B-H and J-P (1 week each). The first dates are in row 4, but after that the rows with the dates appear in different intervals. Ghost is a hidden sheet I populate with data from another sheet. In Ghost Column C is dates and column F is data I want to display on Calendar. What I would like to happen is when information is added to Ghost the code will go to the calendar find the associated date on the calendar that matches the Ghost data and date and put the data in the first empty cell underneath the date on Calendar. Advance warning I am not an expert on VB so please be gentle. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last row of data and paste | Excel Programming | |||
Find next empty row & paste data | Excel Programming | |||
Find matching date in another worksheet, copy and paste data | Excel Discussion (Misc queries) | |||
Find, Match data and paste data between two workbooks | Excel Discussion (Misc queries) | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming |