Copy data from an entire row in Excel 2003 sheet to another sheet in the same workbook automatically

25 pts.
Tags:
Excel worksheets
Microsoft Excel
Microsoft Excel 2003
Microsoft Excel formulas
I have an excel workbook that has a Year To Date summary in it. People enter new data into the YTD summary (along a row there are 15 columns of data to be entered). One of the criteria for the entries is report date. I would like to have other sheets in this workbook (Each named a month, ie. jan, feb, mar) pull the entire row data to each sheet based on the month it was reported. Ie. if the report date was 01/16/09 I would like it to pull a copy of the information from the YTD sheet and put a copy into another sheet labeled JAN. If the report date was sometime in February, I would like it to pull any report date for February and put a copy of the info into a sheet named FEB. and so on. There could be 1 entry for a month or 100 entries, but I would like to have it all summed on the YTD page but also have the data broken out into each month sheet for ease of looking at. Is there an easy way to do this? I looked at Pivot tables and really didn't see that it applied easily to this situation. Currently, the design is for the people to enter the data into the YTD sheet and then every couple of days copy the data for that particular month to that months sheet. Then there are formulas on another page that pull data from that month spreadsheet and do calculations for a monthly summary. I would like to simplify the process so that the data only has to be entered the one time into the YTD sheet and the rest is automated.

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is excel syntax for making a cell in one spreadsheet equal to the data in another cell in a different spreadsheet. This means you change one field and the rest of the data changes.

For example, cell A5 in “Yearly Data” contains value “=’December”!A8″.

A5 of Yearly Data now is equivalent to A8 of December.

Hope this helps!

-Schmidtw

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Wiggetssis
    I am not so sure this solution applies. The data in my sheet row would look like this: 11-11111 Couch 2/27/2007 Jones ARSON Arson II YES OSFM Coos 11-55555 Stern 1/27/2009 Smith PEC Arson II YES OSFM Joe 10-12345 Leon 1/15/2009 Stella ARSON Arson II YES OSFM Cooper 11-00025 Couch 2/27/2009 Jones ARSON Arson II YES OSFM Coos 11-00025 Lee 3/03/2009 Johns MEC Arson II YES OSFM Ruth Based on the date which is the 3rd column over, I want to leave the original entry on this sheet and I want to put a copy of the entire data sorted onto sheets with the month. So I would have a sheet YTD which would have the above data, then I want a sheet JAN (which would contain all of the current year and past year cases that are still open, so the data on the JAN Sheet would be: 11-11111 Couch 2/27/2007 Jones ARSON Arson II YES OSFM Coos 11-55555 Stern 1/27/2009 Smith PEC Arson II YES OSFM Joe 10-12345 Leon 1/15/2009 Stella ARSON Arson II YES OSFM Cooper And the data on the FEB sheet would be: 11-00025 Couch 2/27/2009 Jones ARSON Arson II YES OSFM Coos and the data on the MAR sheet would be: 11-00025 Lee 3/03/2009 Johns MEC Arson II YES OSFM Ruth What I am trying to avoid is having to manually do that and have excel do it automatically to avoid going in every couple of days and copying and pasting the info into the month sheets from the YTD sheet. Hopefully this makes my question make more sense...
    25 pointsBadges:
    report
  • Wiggetssis
    Also, the data is not entered in JAN or FEB sheets, it is entered into the YTD sheet and then copied from there, so data might be input into the YTD sheet out of order and of course there is no way to tell how many reports there will be each month, so I cannot do a fixed formula other than to over guess how many cells will be used like if I think there will be 300 reports in a year, then I will format to 1000 cells down the column to be sure it is all formatted for the end user.
    25 pointsBadges:
    report
  • Chippy088
    ok, I understand where you are coming from. When I have to do things like this I find it easier to try to work it out in pseudo code first. For purposes of this discussion, I'm calling the main sheet ytddata, and each sheet is month 01, 02 03 etc open workbook open ytddata sheet sort sheet by month column ascending for each non zero cell in column a read column3(date) select month.indicator open sheet(month) locate first empty cell in column a copy all datacells from row in ytddata sheet to this row next column of ytddata sheet delete all rows of data on ytddata sheet (not including column headings) when it's defined in this way, I think it helps for coding in VBA (or code of your choice), the sorting of the date column in the ytddata sheet might help speed it up, but I think it is optional. Assuming that you are doing the copying periodically(daily, wekly?), you might want to zero the ytddata sheet to speed things up after copying the data to the individual month sheets. otherwise there will be duplicated data on the month sheets.. This makes more sense than the cell A5 in "Yearly Data" contains value "='December"!A8" solution, as if the data in ytddata sheet is reset to zero, then all the data rows in the month sheet will be effectively zeroed as well. The key word is 'copied, not 'equal to' as was suggested. All you need to do now is the coding. Hope this helps. Dave
    4,625 pointsBadges:
    report
  • SbElectric
    Ok, Wiggetssis – I spent last night to solve your problem of copying data on various worksheets. And I tested - it works! I have saved it as Excel 2003 so it should work for you (I have Excel 2007). Examining your test data I assumed the data contains in 9 columns (Col A thru I) starting with Row 2. Row 1 may have header information. On K2 enter =Month(C2). Now click on lower handle on K2 and drag it all the way to end of data row. This will populate the Month number based on Col C date information. Now create the additional 12 worksheets – Jan thru Dec on the same spreadsheet. The VB code to copy the data will be as follows: Sub CopyRows() k1 = 1 k2 = 1 k3 = 1 k4 = 1 K5 = 1 K6 = 1 K7 = 1 K8 = 1 k9 = 1 K10 = 1 K11 = 1 k12 = 1 For I = 2 To 500 If Cells(I, 11) = 1 Then Worksheets("Jan").Cells(k1, 1) = Worksheets("YTD").Cells(I, 1) Worksheets("Jan").Cells(k1, 2) = Worksheets("YTD").Cells(I, 2) Worksheets("Jan").Cells(k1, 3) = Worksheets("YTD").Cells(I, 3) Worksheets("Jan").Cells(k1, 4) = Worksheets("YTD").Cells(I, 4) Worksheets("Jan").Cells(k1, 5) = Worksheets("YTD").Cells(I, 5) Worksheets("Jan").Cells(k1, 6) = Worksheets("YTD").Cells(I, 6) Worksheets("Jan").Cells(k1, 7) = Worksheets("YTD").Cells(I, 7) Worksheets("Jan").Cells(k1, 8) = Worksheets("YTD").Cells(I, 8) Worksheets("Jan").Cells(k1, 9) = Worksheets("YTD").Cells(I, 9) k1 = k1 + 1 ElseIf Cells(I, 11) = 2 Then Worksheets("Feb").Cells(k2, 1) = Worksheets("YTD").Cells(I, 1) Worksheets("Feb").Cells(k2, 2) = Worksheets("YTD").Cells(I, 2) Worksheets("Feb").Cells(k2, 3) = Worksheets("YTD").Cells(I, 3) Worksheets("Feb").Cells(k2, 4) = Worksheets("YTD").Cells(I, 4) Worksheets("Feb").Cells(k2, 5) = Worksheets("YTD").Cells(I, 5) Worksheets("Feb").Cells(k2, 6) = Worksheets("YTD").Cells(I, 6) Worksheets("Feb").Cells(k2, 7) = Worksheets("YTD").Cells(I, 7) Worksheets("Feb").Cells(k2, 8) = Worksheets("YTD").Cells(I, 8) Worksheets("Feb").Cells(k2, 9) = Worksheets("YTD").Cells(I, 9) k2 = k2 + 1 ElseIf Cells(I, 11) = 3 Then Worksheets("Mar").Cells(k3,1) = Worksheets("YTD").Cells(I,1) Etc. k3 = k3 + 1 ElseIf Cells(I, 11) = 4 Then Worksheets("Apr").Cells(K4,1) = Worksheets("YTD").Cells(I,1) Etc….. k4 = k4 + 1 Etc …. ETC ElseIf Cells(I, 11) = 12 Then Worksheets("Dec").Cells(k12, 1) = Worksheets("YTD").Cells(I, 1) Worksheets("Dec").Cells(k12, 2) = Worksheets("YTD").Cells(I, 2) Worksheets("Dec").Cells(k12, 3) = Worksheets("YTD").Cells(I, 3) Worksheets("Dec").Cells(k12, 4) = Worksheets("YTD").Cells(I, 4) Worksheets("Dec").Cells(k12, 5) = Worksheets("YTD").Cells(I, 5) Worksheets("Dec").Cells(k12, 6) = Worksheets("YTD").Cells(I, 6) Worksheets("Dec").Cells(k12, 7) = Worksheets("YTD").Cells(I, 7) Worksheets("Dec").Cells(k12, 8) = Worksheets("YTD").Cells(I, 8) Worksheets("Dec").Cells(k12, 9) = Worksheets("YTD").Cells(I, 9) k12 = k12 + 1 End If Next I End Sub I do recognize this is not the most elegant code! I was unable to copy the entire row at a time (instead of each cell) – which should be possible. I have the whole Excel spreadsheet along with the buttons to execute the macro to copy the data and also to clear the data (Jan-Dec, if needed). I will be happy to e-mail to you, if needed. Just e-mail me at sbhixson@bellsouth.net But I am not sure why you could not get the needed answer by using Pivot table. Pivot table should be an ideal tool with this type of data. Let me know what type of report/data you are looking – I will explore via Pivot table. Good luck & let us know if this is information is of any benefit.
    2,540 pointsBadges:
    report
  • Chippy088
    nice solution sbElectric. Have you thought about using the range function? instead of If Cells(I, 11) = 1 Then Worksheets(”Jan”).Cells(k1, 1) = Worksheets(”YTD”).Cells(I, 1) Worksheets(”Jan”).Cells(k1, 2) = Worksheets(”YTD”).Cells(I, 2) Worksheets(”Jan”).Cells(k1, 3) = Worksheets(”YTD”).Cells(I, 3) try If Cells(I, 11) = 1 Then ; check cell value Range("A2:l2").Select ; or however many cells from the row you want to copy Selection.Copy Sheets("Jan").Select ; or whichever sheet you need to copy to Range("A2").Select ; change for the target row Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False try to do the transfer by creating a macro, this will give you a list of the functions used (and their syntax) which you can use as a starting point. the hard part is going to be doing the comparison to decide which sheet the row needs to be copied to. I use macros a lot. they are after all a subset of Visual basic (Visual basic for Applications ..VBA) Alt-F8 create macro Copy the data, then just refine it to do the checks. Dave
    4,625 pointsBadges:
    report
  • SbElectric
    David, thank you for your nice comments. I am glad to know that you followed my very simplified logic!!! Yes, that is exactly what I was trying to do as you suggested. But could not define the nine columns (A to I) as one entity. I need to lookup how Range is used in plain VB. I do not use Macro – may need to learn it also. What surprised and frustrated me is that the statement If Cells(I,11) = 1 Then Worksheets("Jan").Rows(k1) = Worksheets("YTD").Rows(I) - did not work. It did not give any compile error or any error message. But did not copy the data. Just for experiment, when I tried Worksheets("Jan").Rows(k1) = 123.45 - it populated the entire Row with 123.45. Go figure!!!
    2,540 pointsBadges:
    report
  • SbElectric
    May be Sarfaraz Ahmed MS Excel help may be able to tell me how to copy the Row data in one statement (or define 9 columns as one entity) and then copy. Thanks, Ahmed. sbElectric
    2,540 pointsBadges:
    report
  • Dgrainge
    I don't think there's a simple way to do this completely automatically. If you were simply using Excel without VBA, I'd suggest a manual solution using Advanced filter and copy / paste. Let's assume it's permisible to add a colum to your source data table. I have created a month column =MONTH(c2) which yields 1 .. 12. THis makes it much easier to select the data required. You can hide that column if you don't want people messing with it. Giving: code name1 date name2 thing number code3 yes code4 name3 month 11-11111 Couch 27/02/2007 Jones ARSON Arson II YES OSFM Coos 2 11-55555 Stern 27/01/2009 Smith PEC Arson II YES OSFM Joe 1 10-12345 Leon 15/01/2009 Stella ARSON Arson II YES OSFM Cooper 1 11-00025 Couch 27/02/2009 Jones ARSON Arson II YES OSFM Coos 2 11-00025 Lee 03/03/2009 Johns MEC Arson II YES OSFM Ruth 3 (please excuse the messy copy/paste from Excel. You'll also notice I've given each column a unique header name. Necessary for what follows) I have also added, at cell A10, month 2 because I want to find Feb entries I then applied Advanced filtering, but I also recorded it as a macro Range("A1").Select Range("A1:K6").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ ("A10:A11"), Unique:=False Selection.CurrentRegion.Select Selection.Copy Sheets("FEB").Select Range("A1").Select ActiveSheet.Paste Sheets("SHEET1").Select ActiveSheet.ShowAllData This selected the table of data at A1, selected Month=2 as criteria, and then filtered just the Feb data. I then selected the table (and got just the visible part), copied it and pasted it to the FEB sheet. Then I returned the table to its previous unfiltered condition. I now have 2 rows of data in the FEB sheet, and the column headings at the top. How about other Months? cell A11 contains 2, hence we look up month 2. SHEETS("FEB") needs to be changed as well. You could copy/paste this code 11 more times, or you could use a for/next loop. Not only would you have to put the month number into cell A11, you'd have to turn the month number into a name for i = 1 to 12 Range("A11").Select ActiveCell.FormulaR1C1 = i ... ... Sheets(Text(i, "MMM")).Select ... ... Next i Actually Text(i,"MMM") won't work because the first parameter is treated a a date if using MMM format, and 1 isn't a date! For i = 1 To 12 Range("A11").Select ActiveCell.FormulaR1C1 = i Range("A1").Select Range("A1:K6").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A10:A11"), Unique:=False Selection.SpecialCells(xlCellTypeVisible).Select Selection.CurrentRegion.Select Selection.Copy txtSheet = UCase(Application.WorksheetFunction.Text(DateSerial(1990, i, 1), "MMM")) Sheets(txtSheet).Select Range("A1").Select ActiveSheet.Paste Sheets("SHEET1").Select ActiveSheet.ShowAllData Next i This doesn't remove OLD text from the month cheets, it simply pastes in the new stuff. If you skip to each sheet, select the active region round A1 and then delete, you'll clear the decks - and that means a few lines of code extra before pasting the filtered data. If you are careful to place formulae so there is always going to be blank space between the table and the summary, you won;t over-write your monthly formulae. To tidy up: You could extend this code 1. the macro puts codes down column I (month) and removed them afterwards 2. The macro sets both cells A10 ands A11 and removes them afterwards Plainly A10 and A11 aren't appropriate places for these 2 cells to go, in practice. 3. You could use the control toolbox to add a button to the front sheet - and the code I've given you becomes the code for the button activation. Derek
    70 pointsBadges:
    report
  • Dgrainge
    >Just for experiment, when I tried Worksheets(”Jan”).Rows(k1) = 123.45 - > it populated the entire Row with 123.45. Go figure!!! Rows(k1) - you have selected one complete row. In VBA, Range = value causes all cells in that range to be be set to that value Worksheet("JAN").Range("A1:B10") = 99 will set 20 cells to value 99 Worksheet("JAN").Range("A:K") = 99 will set all cells in 11 rows to 99
    70 pointsBadges:
    report
  • SbElectric
    Dgrainge: Good analysis and suggested solution. But still I do not understand the following: 1) Worksheets(“Mar”).Cells(k3, 1) = Worksheets(“YTD”).Cells(I, 1) works to get the cell value 2) But Worksheets(“Mar”). Rows(k3) = Worksheets(“YTD”).Rows(I) – does not work to get row values What is the easy way to get the entire row values copied from one Worksheet to another Worksheet? Or say copy 1st 9 columns (A thru I) of Row 3 from one worksheet to another? Will be looking forward to your answer. Have a great day!
    2,540 pointsBadges:
    report
  • JimSnyder
    I am working on the same problem from a different angle. I am using rows ferom a master sheet and placing them in individual sheets as rows appended to the bottom. I am working on the final syntax of finding the first emopty row at the bottom of the individual spreadsheets, but you can convert the code to handle months easily: Sub MoveDataToSheets() ' ' MoveDataToSheets Macro ' Macro written 2/25/2011 by Jim Snyder ' Dim rowCount As Integer, sheetIndex As Integer Dim LastRow As Range ' Prevent screen updates from slowing execution Application.ScreenUpdating = False rowCount = ActiveCell.CurrentRegion.Rows.Count ' Process each row once copying row to matching location tab For currentRow = 1 To rowCount ' Determine which sheet the row goes to Select Case (Cells(currentRow, "B").Value) Case "ALTAVISTA" sheetIndex = 2 Case "AN" sheetIndex = 3 Case "Ballytivnan" sheetIndex = 4 Case "Casa Grande" sheetIndex = 5 Case "Columbus - Devices (DE)" sheetIndex = 6 Case "Columbus - Nutrition" sheetIndex = 7 Case "Fairfield" sheetIndex = 8 Case "Granada" sheetIndex = 9 Case "Guangzhou" sheetIndex = 10 Case "NOLA" sheetIndex = 11 Case "Process Research Operations (PRO)" sheetIndex = 12 Case "Richmond" sheetIndex = 13 Case "Singapore" sheetIndex = 14 Case "Sturgis" sheetIndex = 15 Case "Zwolle" sheetIndex = 16 Case Else sheetIndex = 1 End Select ' Only if the row cotains a valid location, copy it to location sheet If (sheetIndex > 1) Then Set sheet = Worksheets(sheetIndex) ' Designate target sheet ActiveSheet.Rows(currentRow).Copy ' Copy from master sheet sheet.Activate sheet.Paste Destination:=.Cells(LastRow, 1) ' Paste in location specific sheet End If Next Application.ScreenUpdating = True End Sub
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following