Solved Need to move multiple rows based on one field in each row

February 7, 2012 at 07:22:58
Specs: Windows XP
 I have a spreadsheet with hundreds of records. In row "I", there is a number that is representative of an employee. I need to move the entire row based on that one field.For example, in the below, I need to move Joe Smith and his entire row to sheet 2. I need to move Jason Doe and Jane Does and their entire row to sheet 3 and so on.Name DOB MR NXTASSESS Emp#Joe Smith 12/13/1984 307353 1/30/2012 1Jason Doe 7/31/1920 196986 2/2/2012 2Jane Does 6/29/1949 480904 2/2/2012 2Jim Doesnt 4/7/1942 198878 2/3/2012 3LOST, help!

See More: Need to move multiple rows based on one field in each row

February 8, 2012 at 17:47:36
 I suggest that you try the following:1 - Create a template with 11 sheets2 - Group Sheets 2 - 11 (Select Tab 2, Shift-Click Tab 11)3 - Hide the Columns that you want hidden4 - Enter your Column Headings5 - Ungroup the sheets. (Select any sheet tab other than the current sheet)6 - Press Alt-F11 to open the VBA editor7 - Paste the following code into the pane that opens8 - Create a button in Sheet1 and assign the macro to the button9 - Save the template, Macro enabled10 - Paste your data into Sheet1 and click the button to run the macro11 - Save the resulting sheet as an xlsx sheet, unless you want the macro to be saved with every sheet. Your choice.Note: The code assumes that you have column headings in Sheet1, so it starts searching Column I in Row 2.```Sub CopyRowsToSheets() 'Determine last row with data in Sheet 1 last_srcRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row 'Loop through Sheet 1 rows, starting in Row 2 For srcRw = 2 To last_srcRw 'Calculate destination sheet by adding 1 to value in Column I dstSht = Sheets(1).Range("I" & srcRw) + 1 'Determine the next empty Row in destination sheet next_dstRw = Sheets(dstSht).Range("I" & Rows.Count).End(xlUp).Row + 1 'Copy the row to the dstination sheet. Range("I" & srcRw).EntireRow.Copy _ Destination:=Sheets(dstSht).Range("A" & next_dstRw) Next End Sub```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#1
February 7, 2012 at 08:05:12
 Copy. Paste. Done.

Report •

#2
February 7, 2012 at 08:12:56
 Instead of just a Copy/PasteTry doing a Sort on your Column I (there is no Row I, rows are numbered)that should group all the like names together,then do a copy/paste.MIKEhttp://www.skeptic.com/

Report •

#3
February 7, 2012 at 10:03:40
 1) I mean column "I"2) Copy and paste? I have a document with hundreds of records to download every day, sort, copy, paste, save, print and hand out. I am looking for a more automated option. I'm not a rocket scientist, but I know how to copy and paste.

Report •

Related Solutions

#4
February 7, 2012 at 12:42:39
 In your original post, you did not specify that this was going to be more than just a onetime procedure.You are in need of a VBA solution if it is going to be an everyday occurrence.Unfortunately, my VBA skills are just above nil.Hopefully someone will offer a VBA solution.MIKEhttp://www.skeptic.com/

Report •

#5
February 7, 2012 at 15:05:16

Report •

#6
February 8, 2012 at 07:37:24
 Yes, I am humble enough to admit I had an attitude and I apologize for that. I am under a lot of pressure to have this done right away and am at a complete loss. I read the "Copy. Paste. Done" as being sarcastic and in the mood I was in yesterday, was annoyed. I am desperate for a solution and know zilch about vba.

Report •

#7
February 8, 2012 at 08:20:17
 What exactly is in Column I? Is it 1, 2 and 3 as posted?If so, is each number really 1 less than the sheet position it is to be moved to? e.g. all 1's go to Sheet2, all 2's go to Sheet4, etc.?The reason I ask is because VBA needs to be very specific about what it is looking for and what it does when the item is found. It's just like us...it can't "guess" at what the user wants, it needs to be told.The more detail you provide, the better chance a solution can be offered the first time, without a lot of back and forth.Are there any more details that you think we need to know?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#8
February 8, 2012 at 12:42:43
 Hi,What I do is download a file from a website to excel. It downloads 9 columns.There are rows in the file. In column "I" there is a number that represents an employee. The numbers are 1-10.I need to move all rows that have a #1, to Sheet 2. #2, to Sheet 3, #3 to Sheet 4, etc.This will allow for each employee to have a list of their patients.Ideally, I would also like to hide columns A, B, F and G if possible when the rows are moved.

Report •

#9
February 8, 2012 at 12:50:29
 It's all about the details...Do Sheets 2 - 11 already exist or do they need to be created?Are you appending data to the bottom of a sheet or is it new data each time?Are there column headings on Sheets 2 - 11?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#10
February 8, 2012 at 13:17:04
 Whichever is easier, we can create a template and paste the information into sheet 1 every time and have all of the sheets already existing, OR, we can create them each time. Whichever you think is best.New data each time.Yes, there would be headings. If A, B, F and G were hidden the remaining columns would be:C: SortNameD: DOBE: FuncData (which Ideally, would say MRN after moving, or could stay the same)H: Next Assessment DueI: CM Number

Report •

#11
February 8, 2012 at 17:47:36
 I suggest that you try the following:1 - Create a template with 11 sheets2 - Group Sheets 2 - 11 (Select Tab 2, Shift-Click Tab 11)3 - Hide the Columns that you want hidden4 - Enter your Column Headings5 - Ungroup the sheets. (Select any sheet tab other than the current sheet)6 - Press Alt-F11 to open the VBA editor7 - Paste the following code into the pane that opens8 - Create a button in Sheet1 and assign the macro to the button9 - Save the template, Macro enabled10 - Paste your data into Sheet1 and click the button to run the macro11 - Save the resulting sheet as an xlsx sheet, unless you want the macro to be saved with every sheet. Your choice.Note: The code assumes that you have column headings in Sheet1, so it starts searching Column I in Row 2.```Sub CopyRowsToSheets() 'Determine last row with data in Sheet 1 last_srcRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row 'Loop through Sheet 1 rows, starting in Row 2 For srcRw = 2 To last_srcRw 'Calculate destination sheet by adding 1 to value in Column I dstSht = Sheets(1).Range("I" & srcRw) + 1 'Determine the next empty Row in destination sheet next_dstRw = Sheets(dstSht).Range("I" & Rows.Count).End(xlUp).Row + 1 'Copy the row to the dstination sheet. Range("I" & srcRw).EntireRow.Copy _ Destination:=Sheets(dstSht).Range("A" & next_dstRw) Next End Sub```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#12
February 9, 2012 at 12:54:59
 OMW. Works absolutely perfect.I CANNOT thank you enough. You have completely made my day (more like my month). Thank you SO much.Sorry for my cranky earlier attitude.I would send you chocolates if I knew where to send them:) Thanks again.

Report •

#13
February 9, 2012 at 17:00:02
 Glad I could help.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •